Looping through cells and applying conditional formatting

[stroberaver]

Hi,

My problem is that we work with sets of data in Excel, and I would like to automate the checking/verification by highlighting cells that are blank, or should not be blank, or do not meet various criteria.

I had successfully written code that worked on a single column, going down the rows, but expanding this to multiple columns has proved tricky.

Because the datasets vary hugely in length and width with potentially missing bits, empty columns, etc, I am providing input boxes for the user to manually indicate the last row & last column, rather than automatic detection. The only constant is that the data begins in cell A4.

Attached is a sample data file. The code below simply asks for the last row, last column, then is supposed to loop through the range of cells, checking each time whether the value in the first row for that column is a 1, 2 or 3. This value will dictate the type of formatting to be applied, as different columns need checked for different things. For now, I'm just keeping it simple and using one value, which is just intended to highlight any blank cells.

But it doesn't work, and I can't see why not. All this code does is fill the currently selected cell with red.

Please Login or Register  to view this content.

Obviously I will expand the for .. each loop with different header codes once the mechanism is working correctly, but I can't see why it shouldn't at least check through the given range.

Any help greatly appreciated.

PS. This is in Excel 2000. And if the macro would work as intended, I would expect cell D7 in sample.xls to be highlighted in red as it is missing.

[Bob Phillips]

Not tested but I did notice something

Please Login or Register  to view this content.

[stroberaver]

Thanks! That little tweak gets the range looping working correctly, but it's just filling every cell with red, regardless of the contents of "Header" or whether the cell is actually blank.

I've tried replacing the ActiveCell references inside the loop with iCell references, but it performs just the same. Somehow, the bit of code below is qualifying every cell as blank and with header = 1.

Please Login or Register  to view this content.

[Bob Phillips]

I see the problem, you have wrongly declared Header

Please Login or Register  to view this content.

but you can also do it without prompting the user

Please Login or Register  to view this content.

[stroberaver]

I see the problem, you have wrongly declared Header

Brilliant, thank you. This now works correctly.

I must confess I don't entirely understand this change though. I defined "Header" as a range because I thought it was a cell reference, but I get the impression it needs to be set as Long because of what it contains, rather than what it is?

but you can also do it without prompting the user

Thank you, although as mentioned I'm deliberately leaving this to manual specification since the data may often contain blank rows or columns where records or fields are missing, and I don't wish to rely on Excel to decide where the data ends.

However I do like the select case method of processing the "Header" value, many thanks for reminding me about this - more efficient than all the "if" statements I was planning!

[Bob Phillips]

Brilliant, thank you. This now works correctly.

I must confess I don't entirely understand this change though. I defined "Header" as a range because I thought it was a cell reference, but I get the impression it needs to be set as Long because of what it contains, rather than what it is?

Header is just a variable, and you could declare it as a range object and set it to the cell itself

Please Login or Register  to view this content.

but then you would refer to Header.Value in the code, not just Header.

Your code at present is actually setting Header to the cell Value not the cell itself. Value is the default property, so it can be omitted, but this is a great example of why you shouldn't assume defaults. So it is better to write

Please Login or Register  to view this content.