Advice

My question is quite straightforward - or at least, it should be!

I have an Excel sheet with dates and weekdays laid out across the top three rows. I want to highlight all columns in a particular colour where the weekday row (row 3) contains "Sat" or "Sun". This is quite straightforward in a basic sense, using the Conditional Formatting formula:

IF(OR(G$3="Sat",G$3="Sun")

... and applying a background colour. However, I want to be able to copy & paste this formatting across the entire year's worth of dates (365 columns). Obviously, I need to reference relative cells. But it's not working. When I copy the formatting, it defaults back to G$3.

I want row 3 (so $3 should be correct). But the column should change. Without the $ sign, the letter should change. But it's not changing.

Why is this happening? How can I make the reference relative: i.e, reference the current column's Row 3, to see if it's "Sat" or "Sun"?

Improve this question

8 Replies 8

Mayukh Bhattacharya
•

You need absolute references, instead of $G3 use G$3, therefore select all the columns you want to format and then use this formula in CF:

=OR(G$3="Sat", G$3="Sun")
Improve this answer
Lais Ribeiro
•

How to fix it

Use a simple formula like this:

=OR(A$3="Sat", A$3="Sun")

In Applies to, select the entire row where the rule should apply, for example:

=$1:$1

When the “Applies to” range covers the whole area you want, Excel automatically adjusts the column reference for each cell.

Hope this helps!

Image with exemple for you

Improve this answer
Chris Melville
•

@Mayukh Bhattacharya - Thanks, but despite how this seems logical, it's not working for me.

I have applied the Conditional Formatting formula:

="IF(OR($G$3=""Sat"",$G$3=""Sun"")"

to rows:

=$5:$500
  • but it's not working. Any other ideas please?

(By the way, I can't seem to respond to answers here on Stack Overflow - despite me having used the site for many years. There's been a recent change, but I don't know why it's affecting my ability to comment) on answers.)

Improve this answer
FunThomas
•

The parentheses of your formula are unbalanced.
Either you write

="IF(OR($G$3=""Sat"",$G$3=""Sun""))"

Or simplify it by removing the IF:

="OR($G$3=""Sat"",$G$3=""Sun"")"
Improve this answer
Mayukh Bhattacharya
•

Remove the quotes added more than once it should be like this like i have already mentioned. "Sat" and not ""Sat"" and the IF() function isn't making any sense. You don't need it at all.

So you have: ="IF(OR($G$3=""Sat"",$G$3=""Sun"")" instead use this =OR($G$3="Sat", $G$3="Sun")

Improve this answer
Chris Melville
•

Mayukh Bhattacharya - thank you.

I have tried this:

=OR(G$3="Sat", G$3="Sun")

... However, the highlighting is working for Sun and Mon columns, not Sat and Sun. Not sure where I'm going wrong! Any further advice would be appreciated, thank you.

Improve this answer
R vE
•

@Chris

Make sure that the "Applies to" range starts in column G. That is, the same column as the rule. I suspect yours starts in column H, causing the rule to be applied on column shifted to the right.

Improve this answer
Chris Melville
•

R vE

Thank you very much! It's all working now.

Another, non-Excel question is why I am no longer able to respond to answers here on Stack Overflow. I can see links for "Share, Edit, Follow, Flag" - but no "Add Comment". I have been using Stack Overflow for many years, and it's only in the last couple of days that I have been unable to add comments. I thought it might be something to do with being at work, but I also tried at home (on Firefox and Edge) - no no avail. Does anyone know why comments can't be added now?

Improve this answer

Your Reply

By clicking “Post Your Reply”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.