MS Excel Conditional Formating 1

[beanxx]

I can very easily apply conditional formating to create up to three conditions, but how can I create in excess of three seperate conditions?

 

[Koolaid99]

I would say write a macro. If you need help with the code, try explaining what your trying to do. I'm sure I or someone else could help you with what your trying to accomplish.

 

[beanxx]

Thanx. Basically I want to shade a cell based on 4 different text strings. 'Outage' = Red, 'No Protection' = Amber, 'No Redundancy' = Amber, 'Other' = Cyan. I can run conditional formating for up to three conditions but I don't appear to be easily able to do this for a 4th. Since I would like 'No Protection' and 'No Redundancy' to return the same cell shading perhaps Ideally a condition 'Cell Value' Is In the List would be a valid argument but conditional formating doesn't appear to have this option? I suppose 'Formula Is' could do this but I aint sure how?

 

[xlbo]

Hi - From thread68-223068

Acron posted this (which I have altered slightly to be a little more suitable)

Private Sub Worksheet_Calculate()
Dim oCell As Range
For Each oCell In Range("A1:A20&quot
Select Case oCell.text
Case "Outage"
oCell.Interior.ColorIndex = 3
Case "No Protection","No Redundancy"
oCell.Interior.ColorIndex = 44
Case "Other"
oCell.Interior.ColorIndex = 33
Case else
oCell.Interior.ColorIndex = xlnone
End Select
Next oCell
End Sub

That code must be placed in the codemodule od teh actual sheet you are working with. To that select the sheet in question, right click on the sheet tab and select View Code. You can then place code above in the resultantt code pane. You will need to change the range references (I have used A1:A20 in the example), to suit the range you are working with. You can also change the values applied to the color index to suit your own requirements.

For a non VBA solution, Euskadi proposed the following - alter to suit your needs

I'd like to throw out a non-VBA alternative for those who come across this issue and who only have 5 criteria to deal with...

If you use three conditional formats (for 1, 2, and 3), you can add in a custom number format for the other 2 conditions:

[Red][=4]General;[Blue][=5]General;General


I know this doesn't solve your issue with 7 conditions, but for folks who have just 4 or 5 conditions, this will get them some formating without using VBA

HTH
~Geoff~

 

[PeterMoran]

Hi beanxx,

If C5 is a cell you want to apply this conditional formatting to then try this:

Condition 1
Formula is: =C5="Outage"
Set formatting to Red

Condition 2
Formula is: =OR(C5="No Protection",C5="No Redundancy&quot
Set formatting to Amber

Condition 1
Formula is: =C5="Other"
Set formatting to Cyan

This should do what you want.

Good Luck!

Peter Moran

 

[beanxx]

Thanx for this guys. I'm sorted now!

Justin Godson

 

[xlbo]

Hi Justin - just a quicky but the general way of saying thanks to someone that helped you is to award a star - just click on the "Click here to mark this post as a helpful or expert post"
I reckon Peter helped you most here so you may want to award him a star....or not, it's entirely up to you ;-) just thought I'd let u know
HTH
~Geoff~