Conditional Formatting (more than 3) in Excel

Solved/Closed
allanabanana Posts 2 Registration date Sunday August 31, 2008 Status Member Last seen August 31, 2008 - Aug 31, 2008 at 07:15 PM
 Waynef - Nov 14, 2010 at 10:03 PM
Hello,
i am trying to apply more than three options to excel that changes the background colour of the cell.

my range is R7:R1000

i need a VBA or macro that will change the background colour of the cell as follows

if cell reads "Extreme", change background to red

If Cell reads "High" change background to Purple

If Cell reads "Medium", change background to yellow

If cell reads "Low" change background to green

If cell is empty, do nothing

any assistance would be much appreciated

THANKS
Related:

16 responses

aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 491
Aug 31, 2008 at 10:54 PM
Hello,
Try with this macro, you have to write in Visual Basic Editor :
Private Sub Worksheet_Change(ByVal Target As Range)
Set MyPlage = Range("R7:R1000")
    For Each Cell In MyPlage
    
        If Cell.Value = "Extreme" Then
            Cell.Interior.ColorIndex = 3
        End If
        If Cell.Value = "Hight" Then
            Cell.Interior.ColorIndex = 4
        End If
        If Cell.Value = "Medium" Then
            Cell.Interior.ColorIndex = 18
        
        End If
        If Cell.Value = "Low" Then
            Cell.Interior.ColorIndex = 6
        End If
        
        If Cell.Value <> "Extreme" And Cell.Value <> "Hight" And Cell.Value <> "Medium" And Cell.Value <> "Low" Then
        Cell.Interior.ColorIndex = xlNone
        End If
        
    Next
End Sub


Hope this is what you want.

Best regards
133
I would like to do something similar but within an equation. So if certain condition is met that there is a numerical result and the result is displayed in a certain format of font colour or cell color. I would like to do this without going into the preset conditional formatting as I have more than 3 criterias.
0
I have a similar problem, where I want an entire row to change color based ona value in that row. I need 6 colors, so Conditional formatting will not work. The macro above works great for the cell containing the variable to be used for the formatting, but how can I change the color of the entire row.

Thanks for the help.
0