Conditional formatting formula required for day of week and a value

laura_pink

New Member
Joined
Aug 21, 2014
Messages
22
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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,017
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
 =AND($C3="Mon",$B3>415)
 

laura_pink

New Member
Joined
Aug 21, 2014
Messages
22
Didn't work I'm afraid. It turned some Saturdays red instead??
Thanks for the suggestion.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,017
Office Version
  1. 365
Platform
  1. Windows
Is row 3 the first row of data?
Also what is the applies to range?
 

laura_pink

New Member
Joined
Aug 21, 2014
Messages
22

ADVERTISEMENT

Yes row 3 is the first line and it runs from 3 to 2734
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,017
Office Version
  1. 365
Platform
  1. Windows
What is the applies to range?
 

laura_pink

New Member
Joined
Aug 21, 2014
Messages
22

ADVERTISEMENT

Everything runs from A3 to C2734 - sorry not sure what you mean I am a novice
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,017
Office Version
  1. 365
Platform
  1. Windows
If you click on Conditional formatting, Manage Rules you will see something like
1600877036967.png

What does it say in the applies to box ( in the above case =$C$1:$I$27)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,406
Office Version
  1. 365
Platform
  1. Windows
When you write a CF formula for a multiple cell range, you need to write it as it pertains to the FIRST cell in that range.
So if you are choosing all of columns B and/or C, you need to write the formula for row 1, i..e
Excel Formula:
=AND($C1="Mon",$B1>415)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,538
Messages
5,523,428
Members
409,522
Latest member
szophie

This Week's Hot Topics

Top