Copy & Paste conditional formatting

happydonut

Board Regular
Joined
Nov 28, 2019
Messages
57
Office Version
  1. 365
Platform
  1. Windows
Hello,

Could someone help me out here?

I have a sheet with the week number in A1 followed by the dates for that week (starting Monday) in B1 to Friday in F1. Each date has 4 cells with value below (i.e. in cells B2 to B5).
I also have the next week number starting in A10, followed by the dates for that week in B10 to F10... and so on.

Furthermore, I have today's date in cell J1, =TODAY()

What I want to do is, if the date in, for instance B1 is equal to todays date (J1), then the cells below B1 (B2 to B5) should have a background color.
I know how to do this (I did put a conditional formating for B2-B5 with a new rule using the 'Use a formula to determine which cells to format' - where I used if B1=J1.

My question is, can I copy and paste this conditional formatting, so that for each date, when it equals todays date, the cells below that date will be formatted as above?
Or do I need to put a new rule for each date?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

EFANYoutube

Active Member
Joined
May 19, 2017
Messages
278
Issue you will have is that the formatting will be correct for B11 but once you copy the formatting down, it will look at B11=J1 instead of B10=J1
I would have your dates in a hidden column on the right somewhere and reference that instead on B10, then the following would work
Excel Formula:
G2=$J$1
 

happydonut

Board Regular
Joined
Nov 28, 2019
Messages
57
Office Version
  1. 365
Platform
  1. Windows
Issue you will have is that the formatting will be correct for B11 but once you copy the formatting down, it will look at B11=J1 instead of B10=J1
I would have your dates in a hidden column on the right somewhere and reference that instead on B10, then the following would work
Excel Formula:
G2=$J$1
Sorry but I'm not following?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,890
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
ABCDEFGHIJ
12628/06/202129/06/202130/06/202101/07/202102/07/202107/07/2021
2
3
4
5
6
7
8
9
102705/07/202106/07/202107/07/202108/07/202109/07/2021
11
12
13
14
15
16
17
18
19
202812/07/202113/07/202114/07/202115/07/202116/07/2021
21
22
23
24
25
Main
Cell Formulas
RangeFormula
J1J1=TODAY()
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:F5,B11:F15,B21:F25Expression=INDEX(B:B,INT((ROW()-1)/10)*10+(ROW()<10))=$J$1textNO
 

EFANYoutube

Active Member
Joined
May 19, 2017
Messages
278
what happens if you do the below for B2-B5 and then copy paste the formatting accross the rest?

Excel Formula:
B2=$J$1
 

Forum statistics

Threads
1,144,162
Messages
5,722,845
Members
422,460
Latest member
VBA_Noob01

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
Top