Conditional Formatting (more than 3) in Excel [Solved/Closed]

allanabanana 2 Posts Sunday August 31, 2008Registration date August 31, 2008 Last seen - Aug 31, 2008 at 07:15 PM - Latest reply:  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
See more 

37 replies

Best answer
aquarelle 7183 Posts Saturday April 7, 2007Registration dateModeratorStatus May 25, 2018 Last seen - Aug 31, 2008 at 10:54 PM
133
Thank you
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

Thank you, aquarelle 133

Something to say? Add comment

CCM has helped 1924 users this month

It worked great. Thanks a ton for the tip.
wat si a 3 values
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 22, 2010 at 06:14 AM
Jiatin bieber, conditional formatting in excel can have at the most three rules. If you have a situation, where you need more than three rules, aquarelle solution is the approach one has to take. I hope thats what you were asking.
This worked brilliantly - thanks!
Hi aquarelle,thanks for your replay . can we use this formulae in excel itself. I want a formulae to use in excel worksheet itself.
Thanks
30
Thank you
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....
13
Thank you
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!
I tried your code for the additional colors. However, when I locked the cells and protected the sheet, it showed an error in the cell.internior.colorindex = xlNone line. I have several cells that are lists and every time I pick from a list, the error occurs but only when the sheet is protected.

Can you help?
Hi
I've managed a VB that changes a cell colour to red if a 1 is present, and no colour if any other number is present.

The only problem is that the cell is calculating whether or not an employee is on leave during a particular week; 1=yes and 0=no - because the cell content is calculated - the colour won't change.

Colour changes only seem to be triggered when a cell's contents are manually changed?

Is there an amendment to the code below to run every time the worksheet is edited / updated / saved?

Thanks

John

Private Sub Worksheet_Change(ByVal Target As Range)
Set MyPlage = Range("K2:BK65")
For Each Cell In MyPlage

If Cell.Value = 1 Then
Cell.Interior.ColorIndex = 3
End If

If Cell.Value <> 1 Then
Cell.Interior.ColorIndex = xlNone
End If

Next
End Sub
8
Thank you
i would like to make it it like this..

if cell is empty the color of cell will change to yellow
anyone
trying to learn VB - May 21, 2009 at 01:49 PM
5
Thank you
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.
0
Thank you
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
just tell me what are you trying to achieve
0
Thank you
how may I buy advance contition formatihg
Here's how I return a value based on multiple criteria....In this case, days since received unless "yes" or "no".

=IF(COUNTIF(A1,"YES"),0,TODAY()-(A2))*IF(COUNTIF(A1,"NO"),0,1)
0
Thank you
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 !!!
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Mar 3, 2010 at 11:45 AM
Which macro are you refering too. Presuming you are talking about aquarelle, on Sep 1, 2008 3:54:18 am BST ?

In that case you can do some thing like this

.
.
.
For Each Cell In MyPlage
myRow = cell.row 'find row of current cell in range (1 and onward)
myCol = cell.column 'find col of current cell (1 and onward)

thisValue = cells(myRow ,myCol ) 'lets say this is A col
adjValue = cells(myrow, mycol+1) ' this would be then B

if (thisvalue=adjValue) then
Cell.Interior.ColorIndex = 3
end if
...


Having said that, why you need macro ? DO conditional formatting

Select col A

goto conditional formatting, and write formula
=AND(A1<>"",A1=B1)

then click on format and make your formatting changes
0
Thank you
How do I change 1000- to -1000 in Microsoft Office Excel?
0
Thank you
i want to use the below two formulae together

=IF(A1="","") & =workday(A1,5), please help me
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 23, 2010 at 09:50 AM
0
Thank you
well you have the formula pretty much there
=IF(A1= "", "", workday(A1,5))
0
Thank you
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
0
Thank you
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?
0
Thank you
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
siaw k israel - May 1, 2010 at 03:33 PM
-1
Thank you
what is the appropriate function in excel can be use to compare three values
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - May 1, 2010 at 04:19 PM
Compare as what. To find maximum, you would be using max. But if you want to see if A1 is same as b1 and c1, then there is no such function directly. But you can use if like

=if(AND(A1=b1, a1=c1), "all three are same", "all three are not same")
-13
Thank you
Try Conditional Formatting from the Menu