Excel Conditional Formatting with More Than Three Conditions 28

[DeLaMartre]

I have a spreadsheet in which I want Excel to apply conditional formatting to cells with seven different conditions, (different colors for each of seven numbers, "1"=blue, "2"=red, "3" =yellow, "4"=green, etc). Unfortunately, Excel limits me to three. I would be most grateful for any help.

 

[hugobossv8]

Please disregard my last post. I figured it out thanks to the following link:

http://www.ozgrid.com/VBA/excel-conditional-formatting-limit.htm

and here's the answer:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
If Not Intersect(Target, Range("A10:X51&quot) Is Nothing Then
For Each oCell In Target
Select Case oCell.Value
Case Is = 1
oCell.Interior.Color = RGB(255, 255, 0)
oCell.Offset(-1, 0).Interior.Color = RGB(255, 255, 0)
Case Is = 0.5
oCell.Interior.Color = RGB(192, 192, 192)
oCell.Offset(-1, 0).Interior.Color = RGB(192, 192, 192)
Case Else
oCell.Interior.ColorIndex = xlNone
oCell.Offset(-1, 0).Interior.ColorIndex = xlNone
End Select
Next oCell
End If
End Sub

Thanks!

 

[KenWright]

Have a star on me for actually taking the time to post the answer, and not leaving someone else to do it. A lot of people wouldn't have bothered.

Best Wishes
Ken.............

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all

----------------------------------------------------------------------------

 

[hugobossv8]

Thanks Ken and Happy New Year!

 

[Cheesem]

I dont think I am overlapping with any of the previous posts, I have found some of them difficult and it is clear that I need a pretty complete answer - not sure if I can stitch bits and pieces together.

I do a lot of work in pivot tables, in which conditional formatting seems to work as the dimensions of the data area change in response to the criteria input. What you cannot do, of course, is predict what happens to the dimensions of that table.

I have despired at the limit of three conditions (four or even five by clever range and default setting). Having read this thread I'm sure someone could help me with VBA code and a macro....

I'd like to be able to set up a little matrix of values (typically numberic) and formats. I'd do this by hand and it would be unique to each worksheet.I would then like to highlight a range with values in it (from the pivot), and then click on a button to apply the "lookup" formatting to the highlighted range.

Does this seem possible?

Thanks,

Mike C

 

[sparx007]

Excellent tip, that I really needed. However I need some more functionality.

Would it be possible to make the criteria and color dynamic.

For example
Case Is = (text/number in cell A1)
oCell.Interior.Colorindex = (background color of cell A1)
and so on, next criteria looks at cell B1

thanks in advance!

 

[xlbo]

Only if you set the formatting in code - if it is set manually, you cannot reference the properties of other cells except for their values

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[sparx007]

OK, but could you post what my condition should be when referencing the value in the cell?
thx

 

[xlbo]

Sorry - I don't really understand your request. Can you start a new thread and gice some examples of how the logic would work...

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[sparx007]

the script you posted earlier:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
Select Case oCell.Value
Case Is = "ARL"
oCell.Interior.Colorindex = 3
Case Is = "P-ARL"
oCell.Interior.Colorindex = 4
Case Is = "S/L"
oCell.Interior.Colorindex = 5
Case Is = "Maternity"
oCell.Interior.Colorindex = 7
Case Else
oCell.Interior.ColorIndex = xlNone
End Select
Next oCell
End Sub

BUT instead of "hardcoding" the conditions (ARL, P-ARL...) the scipt should look in the value of cell (A11) in the same sheet or another sheet?

so something like:
Case Is = Value(A1)
I don't know the right syntax...

thx

 

[xlbo]

gotcha:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
Select Case oCell.Value
Case Is = Sheeets("Sheetname&quot.range("A1&quot.value
oCell.Interior.Colorindex = 3
Case Is = Sheeets("Sheetname&quot.range("A2&quot.value
oCell.Interior.Colorindex = 4
Case Is = Sheeets("Sheetname&quot.range("A3&quot.value
oCell.Interior.Colorindex = 5
Case Is = Sheeets("Sheetname&quot.range("A4&quot.value
oCell.Interior.Colorindex = 7
Case Else
oCell.Interior.ColorIndex = xlNone
End Select
Next oCell
End Sub


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[sparx007]

excellent!

 

[TimPittaway]

Is there any way the above code can be modified to do the conditional formatting using wildcards? eg. If the first few letters of a cell match in the case, something like what the # could do below:

Select Case oCell.Value
Case Is = "ARL###"
oCell.Interior.Colorindex = 3

Any help would be appreciated! Thanks

 

[KenWright]

Just trap the first 3 letters of the ocell.value in the select case statement, eg:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
Select Case Left(oCell.Value, 3)
Case Is = "ARL"
oCell.Interior.ColorIndex = 3
Case Is = "CDE"
oCell.Interior.ColorIndex = 4
Case Is = "DEF"
oCell.Interior.ColorIndex = 5
Case Is = "EFG"
oCell.Interior.ColorIndex = 7
Case Else
oCell.Interior.ColorIndex = xlNone
End Select
Next oCell
End Sub

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[xlbo]

Just a note - can we please not add any more questions to this thread as it is getting extremely difficult to decipher and interpret. If you have a question regarding conditional formatting or any solutions posted here, please start a new thread and copy the relevant bit of code into it - either that or put a link to this thread on the new thread

Thank You

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question