If formula control conditional formatting

  • Thread starter Thread starter Very Basic User
  • Start date Start date
V

Very Basic User

Hello,

Is it possible to say somehing like... If (This cell)=thrid shift then use
these criterie for the cell...(>=5000=Green, Between 4000 and 5000=Yellow,
less then 4000=red), If (This cell = first shift then use other criteria like
(>=12000=Green, etc.)
 
John,

Select the cell with the number (let's say B2). Then use Format / Conditional Formatting.... and
select the "Formula is" option, with a formula like

=IF($A$2="Third Shift",B2>=5000,IF($A$2="Second Shift",B2>=8000,B2>=12000))

where A2 is the cell with "First Shift", "Second Shift", "Third Shift", etc.

If you have a column of "First Shift", "Second Shift", "Third Shift" values, then change $A$2 to
$A2....

I have just guessed, using 5000 as the Third Shift criteria, 8000 the Second Shift, and 12000 the
First Shift....

Format the cell for Green for that condition.

Then for the second condition (yellow) use

=IF($A$2="Third Shift",AND(B2>=4000,B2<5000),IF($A$2="Second
Shift",AND(B2>=6000,B2<8000),AND(B2>=9000,B2<12000)))

Then for the third conditon (red) use

=IF($A$2="Third Shift",B2<4000,IF($A$2="Second Shift",B2<=6000,B2<=9000))


HTH,
Bernie
MS Excel MVP
 
Exactly what I needed to make it work. Thanks!
--
Thank you for your time!
John


Bernie Deitrick said:
John,

Select the cell with the number (let's say B2). Then use Format / Conditional Formatting.... and
select the "Formula is" option, with a formula like

=IF($A$2="Third Shift",B2>=5000,IF($A$2="Second Shift",B2>=8000,B2>=12000))

where A2 is the cell with "First Shift", "Second Shift", "Third Shift", etc.

If you have a column of "First Shift", "Second Shift", "Third Shift" values, then change $A$2 to
$A2....

I have just guessed, using 5000 as the Third Shift criteria, 8000 the Second Shift, and 12000 the
First Shift....

Format the cell for Green for that condition.

Then for the second condition (yellow) use

=IF($A$2="Third Shift",AND(B2>=4000,B2<5000),IF($A$2="Second
Shift",AND(B2>=6000,B2<8000),AND(B2>=9000,B2<12000)))

Then for the third conditon (red) use

=IF($A$2="Third Shift",B2<4000,IF($A$2="Second Shift",B2<=6000,B2<=9000))


HTH,
Bernie
MS Excel MVP
Click to expand...
 
Back
Top