Conditional formatting formula required for day of week and a value

laura_pink

New Member
Joined
Aug 21, 2014
Messages
30
Office Version
  1. 365
Platform
  1. Windows
Hi,
I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of the week i.e. "Mon".
I need to set some conditional formatting so that when column C is "Mon" and column B is greater than 415, then the value in column B turns red.

I have tried this and its not working:
=AND($C$3:$C$2734="Mon",$B$3:$B$2734>415) I have also tried it the other way round =AND($B$3:$B$2734>415,$C$3:$C$2734="Mon")
I have also tried it without the commas around Mon - nothing works.

Any ideas? Many Thanks,
Laura
 
Hi,
I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of the week i.e. "Mon".
I need to set some conditional formatting so that when column C is "Mon" and column B is greater than 415, then the value in column B turns red.

I have tried this and its not working:
=AND($C$3:$C$2734="Mon",$B$3:$B$2734>415) I have also tried it the other way round =AND($B$3:$B$2734>415,$C$3:$C$2734="Mon")
I have also tried it without the commas around Mon - nothing works.

Any ideas? Many Thanks,
Laura
=AND($B3>415, $C3="MON") this one i have tried and its working
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Glad you sorted it & thanks for the feedback.
 
Upvote 0
If the formula from padapinto worked, the formula from post#2 should work as well, they are exactly the same thing. ;)
 
Upvote 0
Thanks for all the replies! Really helpful. I wonder what would the formula be if I wanted to say values between 395 and 415 rather than >415. Tried using : between these numbers and it’s not working
 
Upvote 0
How about
Excel Formula:
=AND($C3="Mon",OR($B3>395,$B3<=415))
 
Upvote 0
Thanks for the suggestion - this highlighted every Monday and not those between that range
 
Upvote 0
Oops, not enough coffee yet :coffee:
Excel Formula:
=AND($C3="Mon",$B3>395,$B3<=415)
 
Upvote 0
Brilliant thanks! you are a genius. Thanks a lot you have saved me hours of time.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top