conditional formatting

[owl527]

Hi,
Is it possible for have more than 3 conditional formatting? I have 4
criteria, High, Medium, Low and Complete. I was hoping to have a
different color for each criteria but was only able to get up to three.
Please help.

Thanks.

 

[Peo Sjoblom]

Why not let unformatted be one of the criteria, that way you would have 4
formats
Otherwise it is only possible through VBA

--
Regards,

Peo Sjoblom

(No private emails please)

 

[owl527]

Hi,
I am not too sure what you mean by unformatted. Do you mind elaborating
on it a little? tks.

 

[mangesh_yadav]

He meant, use the conditional formatting for your 3 conditions, and the
last condition, you don't format. So in any case you will have 4
formats

Mangesh

 

[owl527]

Hi, I would prefer being able to format it. How do u do it in VBA? pls
advise. Thanks very much for your help.

 

[mangesh_yadav]

Use this:

The following code has to go in the module of the sheet concerned.
Right click on the sheet tab, and select view code. and enter the
following code.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then

Select Case LCase(Target.Value)
Case "high"
Target.Cells.Interior.ColorIndex = 3
Target.Cells.EntireRow.Interior.ColorIndex = 3
Case "medium"
Target.Cells.Interior.ColorIndex = 4
Target.Cells.EntireRow.Interior.ColorIndex = 4
Case "low"
Target.Cells.Interior.ColorIndex = 5
Target.Cells.EntireRow.Interior.ColorIndex = 5
Case "complete"
Target.Cells.Interior.ColorIndex = 6
Target.Cells.EntireRow.Interior.ColorIndex = 6
Case Else
Target.Cells.Interior.ColorIndex = 0
Target.Cells.EntireRow.Interior.ColorIndex = 0
End Select

End If

End Sub



Note: Line 2 target.column = 1
This is the column which will have the high, low. If your column is
different say B, change this to 2.
After every case statement, there are 2 lines. please comment one of
them. One highlights the entire row, while the other highlights the
cell only.



Mangesh

 

[RagDyeR]

You might check out this web page of John McGimpsey, where he shows how to
get additional colored fonts using XL's regular formatting features without
the necessity of using macros:

http://www.mcgimpsey.com/excel/conditional6.html
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


message
Hi, I would prefer being able to format it. How do u do it in VBA? pls
advise. Thanks very much for your help.