highlighting a row in a macro

Posted by jason on October 04, 1999 9:58 AM

i modify an excel file that is generated from a database twice a day. i have a macro that does most of the formatting, but i can't figure out how to do a few crucial steps. for starters, i have 5 columns (W-AA) that, if any of the cells are blank, the entire row needs to be highlighted. i used conditional formatting to highlight the individual cell, but i can't get it to highlight the entire row. any ideas?

also, the file doesn't have a static amount of entries, but i want to automatically number the rows. in my macro i insert a column, and fill down with the numbers from 1 to 100 (larger than the file ever gets) and just remove the excess. is there a way to tell the macro to fill to the end of the file?

Thanks,

Jason

Posted by Chris on October 04, 1999 11:31 AM

Jason,

You can loop through the columns and use the following code when you reach an empty cell:

ActiveCell.EntireRow.Select
'Bold the highlighted row
Selection.Font.Bold = True
'Make row yellow
With Selection.Interior
.ColorIndex = 6
End With

The following code will count the number of entries (I used column B because you inserted a column), then fill column A with the correct length. Assuming data starts in row 1.

oRows = Range("B1", Range("B1").End(xlDown)).Cells.Count
For n = 1 To oRows
Range("A" & n).Value = n
Next n

I think that's all you need.

Chris

Posted by Ivan Moala on October 05, 1999 2:28 AM

To highlight the entire row then select the row range, i'm assuming range("A5:V5") and put this
formula in the conditonal formater, make sure
you select Formula is. =OR($W5="",$X5="",$Y5="",$Z5="",$AA5="")
To copy the format to all the other rows use the
format painter.

Chris's option works well

Ivan