PDA

View Full Version : [SOLVED] Conditional formatting



blackie42
09-03-2014, 08:13 AM
Hi,

How can I apply formatting based on whats in col A and Col B e.g

If N in column B and less than 60 in col A - format col A as green

I have (in A1 (in the rules)) Cell Value Less than =(B1="N")=60 (and formatting as green)

but not sure this right?

thanks for any help

Jon

Bob Phillips
09-03-2014, 08:37 AM
Use a formula of

=AND($A1<60,$B1="N")

blackie42
09-04-2014, 01:17 AM
Thanks Bob,

Works OK for the one condition but now want to apply multiple conditional formatting i.e.

Where A less than 60 and B ="N" green
A between 60 and 89 and B ="N" amber
A greater than 89 and B ="N" blue

Had a go but can't get it to work

Can this be done?

thanks
Jon

Bob Phillips
09-04-2014, 01:31 AM
Green: =AND($A1<60,$B1="N")
Amber: =AND($A1<89,$B1="N")
Red:=$B1="N"

Ensure that you tick the stop when met checkbox for each

blackie42
09-04-2014, 08:12 AM
Thanks for reply but just can't get it to work.

Not sure I have the criterias correct? Less than, Less than, Greater than??

thanks again

Bob Phillips
09-04-2014, 08:43 AM
What I have shown you should work, so maybe you could post the workbook.

SamT
09-04-2014, 06:20 PM
@xld He's putting words in your formulas.

@Blackie, imagine an "Else" between those formulas: If less than 60, Else if less than 89, Else anything

@ ALL: These are homework assignments. This is the third(?) question about formatting using these values and these colors. I think that this OP didn't read the assignment all that well; the most complex situation had to deal with 4 conditions and colors, a Div 0 error and work on Excel from XP to 8. Same values (%'s,) same colors.

blackie42
09-05-2014, 01:32 AM
Apologies - I did do a search on CF but there are 16 pages many just say CF - My eyes would go loopy if I had to search every thread for what I need.

Apologies again but I can't seem to find any way of attaching the worksheet

1st rule Cell Value Less than =AND($A1<60,$B1="N") format green stop ticked

2nd rule Cell Value Less than =AND($A1<89,$B1="N") format amber stop ticked

3rd rule Cell Value Greater than =$B1="N" format red stop ticked

When I change the number in A1 to 90 it doesn't change the colur from green

thanks again

blackie42
09-05-2014, 06:06 AM
I got this to work with 'use formula to determine which cells to format'

Cell A1 already coloured green
1st rule =AND($A1>59,$B1="N") amber
2nd rule =AND(($A1>89,$B1="N") red

enter anything up to 59 cell A1 remains green, 60 to 89 changes o amber, above 89 goes to red

If cell B1 = Y then it remains green

thanks for pointing me in the right direction

regards