Help required on conditional formatting

[maddy29]

Hello Friends,

I have uploaded the file on which i was working upon. I have used exact to get the numbers of sales/SALES ( basis on case sensitive ).... but failed to apply conditional formatting as in I wanted SALES to be red and Sales blue or any other color...

Can anyone suggest how to do that..

thanks-Maddy

 

[Ramesh]

Hi Maddy,

I could apply the conditional formatting on these sales by using the following formula

=IF(EXACT(B2,"Sales"),1,IF(EXACT(B2,"SALES"),2,0))

Based on the above formula, we can observe that, when the words Sales matches the text in the cell, it returns 1 otherwise I have written one more IF condition to return 2 to match SALES in the current cell.

Then in the conditional formatting window, I have added 2 conditions. One checks if the formula returns a value 1 then apply blue color and in the second rule check for the formula output value 2 then apply red color.

I am here by sending across the updated file.

Hope this helps!!!

 

[Chihiro]

You'd need 2 conditional format as Ramesh stated.
Formula can be shorter though with 2 different formulas.

Select range B2:B15

First CF: for when it's match for "Sales"
=EXACT($B2,$D$1)

2nd CF: for when it's not match for "Sales"
=EXACT($B2,$D$1)=FALSE

 

[maddy29]

Hi Maddy,

I could apply the conditional formatting on these sales by using the following formula

=IF(EXACT(B2,"Sales"),1,IF(EXACT(B2,"SALES"),2,0))

Based on the above formula, we can observe that, when the words Sales matches the text in the cell, it returns 1 otherwise I have written one more IF condition to return 2 to match SALES in the current cell.

Then in the conditional formatting window, I have added 2 conditions. One checks if the formula returns a value 1 then apply blue color and in the second rule check for the formula output value 2 then apply red color.

I am here by sending across the updated file.

Hope this helps!!!

Hey Ramesh,

awesome.. thanks you so much...it worked

 

[maddy29]

You'd need 2 conditional format as Ramesh stated.
Formula can be shorter though with 2 different formulas.

Select range B2:B15

First CF: for when it's match for "Sales"
=EXACT($B2,$D$1)

2nd CF: for when it's not match for "Sales"
=EXACT($B2,$D$1)=FALSE

awesome... thankyou Chihiro nice one