Change Background Color of Row Based on Cell Value

**ashishashish8** · 11-21-2011, 12:34 AM

Hi,
I want to change the background color of my row according to cell value. For this i have to loop through whole sheet mean say i want to loop through all cell in A say from $A1 to A1000 and where i found word "Absent" row should be red or something like this.

How can i achieve this in Excel.
Thanks in Advance

**foxguy** · 11-21-2011, 12:57 AM

Use conditional formatting:
formula for Cells A1:Z1 "=Instr($A1,"Absent")>0" format color red.

**ashishashish8** · 11-21-2011, 01:10 AM

Thanks for your reply Foxguy..
I want to write a macro for this thing and i also wrote one and it is working fine.
Another thing i would like to know is how i can run this macro for all sheets.
I want to run this thing for all sheets.
What i have wrote yet is.

Sub Update_Row_Colors()

Dim LRow As Integer
Dim LCell As String
Dim LColorCells As String
'Start at row 7
LRow = 1

'Update row colors for the first 2000 rows
While LRow < 200
LCell = "A" & LRow
'Color will changed in columns A to K
LColorCells = "A" & LRow & ":" & "W" & LRow

Select Case Range(LCell).Value

'Set row color to light blue
Case "Loc"
Range(LColorCells).Interior.ColorIndex = 34
Range(LColorCells).Interior.Pattern = xlSolid
Range(LColorCells).Font.Bold = True
Range(LColorCells).Font.Name = "Arial Narrow"
Range(LColorCells).Font.Size = 10.5
Range(LColorCells).EntireColumn.AutoFit

'Default all other rows to no color
Case Else
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = xlNone
Range(LColorCells).Font.Name = "Arial Narrow"
Range(LColorCells).Font.Size = 10.5

End Select

LRow = LRow + 1
Wend

Range("A1").Select

End Sub

**foxguy** · 11-21-2011, 01:14 AM

I can't look at it right now.
Please put your code inside CodeTags to make it more readable.
Also Indent code that is inside For...Next loops, If...End If blocks, etc. Much more readable.

**ashishashish8** · 11-21-2011, 01:19 AM

This is the exact code.
I want to run it on all sheets in excel say i have more than 50 sheets.

    
Sub Update_Row_Colors()

        Dim LRow As Integer
        Dim LCell As String
        Dim LColorCells As String
        'Start at row 1
        LRow = 1

        'Update row colors for the first 200 rows
        While LRow < 200
            LCell = "A" & LRow
            'Color will changed in columns A to K
            LColorCells = "A" & LRow & ":" & "W" & LRow

            Select Case Range(LCell).Value

                'Set row color to light blue
                Case "Loc"
                    Range(LColorCells).Interior.ColorIndex = 34
                    Range(LColorCells).Interior.Pattern = xlSolid
                    Range(LColorCells).Font.Bold = True
                    Range(LColorCells).Font.Name = "Arial Narrow"
                    Range(LColorCells).Font.Size = 10.5
                    Range(LColorCells).EntireColumn.AutoFit
               

                'Default all other rows to no color
                Case Else
                    Rows(LRow & ":" & LRow).Select
                    Range(LColorCells).Interior.ColorIndex = xlNone
                    Range(LColorCells).Font.Name = "Arial Narrow"
                    Range(LColorCells).Font.Size = 10.5

            End Select

            LRow = LRow + 1
        Wend

        Range("A1").Select

    End Sub

**foxguy** · 11-21-2011, 01:32 AM

1st:

Range(WhatEver)

is identical to

ActiveSheet.Range(WhatEver)

Worksheets("MySheet").Range(WhatEver)

refers to the Range(WhatEver) on Sheets("MySheet")

I didn't review your code. I just assume it works.
But it only works on the ActiveSheet. You need to tell it what sheet to work on.

Sub CycleSheets()
    Dim sh as Worksheet
    For Each sh in Worksheets
        Update_Row_Colors sh
    Next sh
End Sub

Sub Update_Row_Colors(ByVal sh As Worksheet)
    'Your code.
    '    Change all " Range(....)"
    '    to " sh.Range(....)"
End Sub