Excel Macro to highlight entire row on a condition.

Hi kwoznica,

There should be no need to use VBA for this. Use Conditional Formatting instead -> http://www.cpearson.com/excel/cformatting.htm

Regards,

Wayne

I would use conditional formatting rather than a macro for this. Say you want the row highlighted if column A = 5, you would do the following:
1. Select the columns you want highlighted
2. Choose Format-Conditional formatting from the menu
3. Change the dropdown to FormulaIs and enter =$A1=5 in the formula box.
4. Press the Format... button and choose the pattern you want.
5. OK out of the dialog.

Regards,
Rory

In this case, conditional formatting wont work, as it only colors the cell which contains the value, and not the entire row.
Here is a macro that will color the entire row:

Sub Color_Only()

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'              Copyright (C) Irial 2007                     '
'Feel free to use this macro as a reference for other macros'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

ASUP = Application.ScreenUpdating
Application.ScreenUpdating = False

    Range("A1:A999").Select
   
        Application.CutCopyMode = False
       
        For Each cell In Range("A:A")
            If cell.Value = 1 Then
            Range(cell.Row & ":" & cell.Row).Interior.ColorIndex = 3
        End If
    Next
    Application.ScreenUpdating = ASUP
End Sub

How to use this macro:
The macro select the cells in the range you tell it to. (Range A1:A999) and checks each and every cell in that range against a value. In my case, it will check each cell to determine id the value is 1. If it finds that particular value, it will color the entire row of that cell red. If it does not find anything, it will leave the row alone.

I hope this helps, and with kind regards,
~Irial

>>"In this case, conditional formatting wont work, as it only colors the cell which contains the value, and not the entire row"

That is not true - it can be made to colour any or all columns in the row that you want if you follow the instructions I gave above.

Regards,
Rory

Hiyas Rory,

In all due honesty, i completely missed your comment.

Your method does indeed work, but i would not want to use this method if i had 1000, or even 100 lines i needed to color. You would have to fill in the formula for each of those lines in the dialog box, which is quite a hassle if you ask me. Also, by default the amount of conditional formats is limited to 3. If you have 100 lines, how would you color them? And even if there was a workaround for that, would you be writing coditional formats on a 1-1 ratio with the amount of lines in the file? Or am i perhaps missing some excel functionality that allows easy execution of these tasks? :)

With kind regards as ever,
~Irial

Irial,

The value that I am looking for is in Column F and = Pending
Do I need to include single quotes around the word pending?

Such as
For Each cell In Range("A:A")
            If cell.Value = 'Pending' Then
            Range(cell.Row & ":" & cell.Row).Interior.ColorIndex = 3
        End If
Thanks.

Irial,
By making the row reference relative but the column absolute, the conditional formatting will look at the value in column A for each row. You can select all the columns you want it done for, then just apply one formula to all of them.
Regards,
Rory

Here's a conditional formatting example: http://www.ee-stuff.com/Expert/Upload/getFile.php?fid=4841
If you enter Pending into a cell in column F, columns A to K for that row will be highlighted in blue. (You could make the entire row highlight if you want)
Regards,
Rory