Conditional Formatting in a formula

  • Thread starter Thread starter Nick
  • Start date Start date
N

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
 
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
 
i want to know the code if i have 6 conditions. conditional formating only
offers up to 3 conditions.please help
 
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
 
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
 
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
 
Back
Top