Conditional Formatting in a formula

[Nick]

How do I format the value of a cell based on the result of an IIF formula?
(I want to change the back & fore colours.)

Thanks

Nick

 

[JulieD]

Hi Nick

(in Excel they're IF functions not IIF)

you need to use conditional formatting to do it
click on the cell and choose
format / conditional formatting
choose cell value is
for example
equals 10
press the format button - choose a formatting (back colours are on the
patterns tab)
click OK
now click ADD
choose cell value is
for example
greater than 20
format as required
click OK twice
to see the result

if you need additional help or you have a more complex IF statement (you can
have up to 3 conditions using this feature, more than that you need to use
code) post back

Cheers
JulieD

 

[rp]

i want to know the code if i have 6 conditions. conditional formating only
offers up to 3 conditions.please help

 

[JulieD]

Hi rp

here's an example of conditional formatting applied to the range A1:A5 with
6 conditions, substitute the numbers next to the word "case" with your
conditions (e.g. Case "cat": ) & change the ci numbers to change the
colours.
****

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngArea As Range
Dim ci As Long

Set rngArea = Range("A1:A5")

If Not Intersect(Target, rngArea) Is Nothing Then
Select Case Target.Value
Case 1: ci = 3
Case 2: ci = 8
Case 3: ci = 24
Case 4: ci = 26
Case 5: ci = 17
Case 6: ci = 44
End Select
Target.Interior.ColorIndex = ci
End If
End Sub

***

let us know how you go

Cheers
JulieD

 

[rp]

It works very fine. What if the condition is in cell b and i like to change
the color of cell a. For ex:
-------
blue for cat
red for dog

b1=cat so a1 should be blue
b2=dog so a2 should be red

 

[JulieD]

Hi rp

change the code as follows:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngArea As Range
Dim ci As Long

Set rngArea = Range("B1:B5") 'change this

If Not Intersect(Target, rngArea) Is Nothing Then
Select Case Target.Value
Case 1: ci = 3
Case 2: ci = 8
Case 3: ci = 24
Case 4: ci = 26
Case 5: ci = 17
Case 6: ci = 44
End Select
Target.offset(0,-1).Interior.ColorIndex = ci 'change this
End If
End Sub

 

[rp]

thank you very much! it works exactly what i wanted.

 

[JulieD]

Hi rp

i'm glad - thanks for the feedback.

Cheers
julieD