1

I am trying to copy conditional formatting across the whole worksheet.

The conditional formatting is based on 2 cells, 1 cell which will say either 'on-hire' or 'off-hire' and the other cell with a date.

I have created a formaula in which if the column says 'off-hire' and the date column is older than today's date then the whole row will turn red.

=AND($G$8<TODAY(),$H$8="on-hire")

I want to copy this across the whole worksheet without manually entering the conditional formatting for each row.

I've tried the ways I've seen online using the formatting painter etc. but it doesn't seem to work.

Does anyone on here have any solutions?

1 Answer 1

2

You likely just need to tweak your cell references in your formula. You are using an absolute reference ($G$8 for example). Change to $G8.

Also, you're explanation says you want to impact off-hire, but your formula has on-hire. I'm going forward with on-hire from here...

Select the entire range you want to format. In this case, I selected A2:D9.

Create your Conditional format rule.

=AND($C2<TODAY(),$D2="on-hire")

Adjust your format rules, and you're done. Here, we're changing the row to red if the person is on-hire and has a date less than today.

enter image description here

2
  • 1
    Key point: Make the relative range references (those without $) be the right references for the top-left cell in the range. Feb 18, 2021 at 20:11
  • That worked, thank you very much.
    – Cdwyer
    Feb 20, 2021 at 14:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.