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

"Nick" <(E-Mail Removed)> wrote in message
news:uru$(E-Mail Removed)...
> 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
>

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

"rp" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>i want to know the code if i have 6 conditions. conditional formating only
> offers up to 3 conditions.please help
>
>

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
-------
Do i need to change the whole code or just little part of it? Thanks again!

rp

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
> -------
> Do i need to change the whole code or just little part of it? Thanks
> again!
>
> rp
>
>
>

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

Hi rp

i'm glad - thanks for the feedback.

Cheers
julieD

"rp" <(E-Mail Removed)> wrote in message
news:%23WLHi%23$(E-Mail Removed)...
> thank you very much! it works exactly what i wanted.
>
>