Conditional Formatting (more than 3) in ExcelSolved/Closed

Question

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

aquarelle · Accepted Answer

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

blitz · Answer

i would like to make it it like this..

if cell is empty the color of cell will change to yellow
anyone

Steve · Answer

you could try this..
in the cell you want to colour put the cond format based upon the fisrt cell that will result in the adjacent cells being coloured

=COUNTIF(AE10,"HD") + COUNTIF(AE10,"H") + COUNTIF(AE10,"T1")+ COUNTIF(AE10,"T2") + COUNTIF(AE10,"T3") + COUNTIF(AE10,"T4")+ COUNTIF(AE10,"T5")+ COUNTIF(AE10,"T6")+ COUNTIF(AE10,"T7")+ COUNTIF(AE10,"T7.5")

or the shorter version..
=SUMPRODUCT(--(LEFT(J18,1)="H")) + SUMPRODUCT(--(LEFT(J18,1)="T"))

just copy and paste the format onto the cells you want to cond format to be active for....

LostLogic · Answer

I encountered a similar "problem" if you will today. After hacking a bit this is what I ended up with.

' Run macro automaticly every time the Worksheet is changed
Private Sub Worksheet_Change(ByVal Target As Range)

' Define the range we wish to check against for x'es and v's.
Set ColorMagic = Range("C5:C24")

' Start checking each cell in the target range named ColorMagic for v's, x'es and nothings
For Each Cell in ColorMagic

If Cell.Value = "x" Then ' Color it red
Range("A" + CStr(Cell.Row) + ":C" + CStr(Cell.Row)).Interior.ColorIndex = 3
ElseIf Cell.Value = "v" Then ' Color it green
Range("A" + CStr(Cell.Row) + ":C" + CStr(Cell.Row)).Interior.ColorIndex = 4
else ' Remove all color
Range("A" + CStr(Cell.Row) + ":C" + CStr(Cell.Row)).Interior.ColorIndex = xlNone
End If

Next

End Sub

Now... This will change the cells in the range A to C on the checked row to Red if the value of the column C equals x, Green if it equals v and no color if it's anything else. This can be modified quite easily by adjusting the ranges and so fourth.
Hope this is of use to someone. :)
Cheers!

trying to learn VB · Answer

I understand how to use the macro to color many different colors within a spreadsheet, but if there are already some colors on the sheet, how would I tell the macro to leave the existing color if it has run through all of the tests and none of them apply.  For example, at the end of the macro: it says
Case Else
icolor = 0
This will take out the color of any cell that doesn't meet any criteria.  How would I tell it 
Case Else
"just leave the cell alone and don't change the color"

Thank you for any advice.

vb_novice · Answer

I came across the code above from OldYgg and it works good for me except that I need to make it conditional.  How can I put these two statements together

If cell.Column = 3 Then
Select Case cell.Value
Case Is <> "Total"

"and"

If cell.Column = 4 Then
Select Case cell.Value
Case "Total"
myRange.Rows(cell.Row).Select
    With Selection.Interior
        .ColorIndex = 41
        .Pattern = xlSolid
    End With
End Select

'This colors in the entire Row in Range
'myRange.Rows(cell.Row).Interior.ColorIndex = iColor

End If
Next
End Sub

TSIOUSTAS · Answer

how may I buy advance contition formatihg

tk · Answer

Dear all,

I found that the Marco is vvvv useful. However, what if I want to change the function a bit?

For example, I have got 2 cells in different columns e.g. A1 and B1. I would like to change the font color of A1 if A1 and B1 equals to exact wordings. 

How should I modify the code then? Many Thanks for your help !!!

Tongo · Answer

How do I change 1000- to -1000 in Microsoft Office Excel?

siaw k israel · Answer

what is the appropriate function in excel can be use to compare three values

Lucky · Answer

i want to use the below two formulae together

=IF(A1="","")       &  =workday(A1,5),         please help me

rizvisa1 · Answer

well you have the formula pretty much there
=IF(A1= "", "", workday(A1,5))

muhammad · Answer

Dim I as integer
For I = 0 To 1000
    Cells(7 + i, 18).Select
    If Cells(7 + i, 18).Value = "Extreme" Then
        Selection.Interior.ColorIndex = 3
    ElseIf Cells(7 + i, 18).Value = "High" Then
        Selection.Interior.ColorIndex = 13
    ElseIf Cells(7 + i, 18).Value = "Medium" Then
        Selection.Interior.ColorIndex = 6
    ElseIf Cells(7 + i, 18).Value = "Lowhen
        Selection.Interior.ColorIndex = 4
    Else
        Selection.Interior.ColorIndex = 2
    End If
Next i

Cosmin · Answer

Try Conditional Formatting from the Menu

Memphis · Answer

Ok, I am having a tough time with one thing.  in the Icon set of conditional formatting, I enter for the first tab =IF(SUM($B$6+$B$5)>5) and then in the type I put number, it doesnt seem to do what I want it to do.  Basically I want two cells added together and if that number is >5 then I want the icon to show as full bars.  Anyone have any suggestions for me?

Waynef · Answer

Option Explicit 

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
' Conditional Formatting for more than 3 conditions 

    Dim rng As Range 

'For example if the range is R7 to R1000 

    Set rng = Intersect(Target, Range("R7:R1000")) 
    If rng Is Nothing Then 
        Exit Sub 
    Else 
        Dim cl As Range 
        For Each cl In rng 
            On Error Resume Next 
            ' -- The line above won't change the cell's background 
            ' -- color if the cell's value is not found in the range 
            ' -- that we specified (rngcolors). 

'For example if the Worksheet is Attendance  
'Define a Range - For Example: you would need to enter this in the worksheet area 'as a legend and define a name for it as RngColor also in the Cells R7 to R1000 as a Dropdown - You can do this via conditional 'formating - list 
'RngColor 
'Extreme 3 
'High 29 
'Medium 27 
'Low 4 

            cl.Interior.ColorIndex = _ 
                Application.WorksheetFunction.VLookup(cl.Value _ 
                , ThisWorkbook.Sheets("Attendance").Range("RngColor"), 2, False) 

            If Err.Number <> 0 Then 
                cl.Interior.ColorIndex = xlNone 
            End If 
        Next cl 
    End If 

End Sub 

Mail address deleted for your security and because all replies must be done on the forum

Conditional Formatting (more than 3) in Excel

16 responses

Similar discussions

Subscribe To Our Newsletter!