I have an excel sheet which has a number of courses along the top and people down the left hand side.

Underneath the course name, I have the deadline for the course to be completed and in each person row, we have the date the person last took the course.

I need one conditional formatting rule to apply to the whole sheet which checks if the value in the cell is greater than the deadline date for that column and highlights it red, the only way I can seem to get it working is to have one rule per column, but to keep things clean I wondered whether there's a way I can avoid doing this?

I have attempted to write a formula to do this but it kept checking if the whole range was greater than B1, instead of B1, C1, D1 etc..

I have included an example of how the sheet looks to help with the solution:


  • 3
    Select B4:J23. Call "Conditional formatting" - "By value". Enter the condition for cell value > B$2. Set highlighting cell format. Apply. – Akina Aug 9 at 7:47
  • In VBA it is Range("B4:J23").FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=B$2" – Akina Aug 9 at 7:52
  • Please Edit your post and reload another Screen shot ,, which could open to view on click ! – Rajesh S Aug 9 at 8:44

Your Answer

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

Browse other questions tagged or ask your own question.