Copy/pasting Conditional Formatting to new rows?

[BadSpreadsheet]

Hello,

I have a regular table of numbers and have manually selected the top left most cell (first column/first row) and every second cell along that first row (by holding down the CTRL key) after which I applied one of the simple preset colour gradient Conditional Formats to. Consider this manually formatted row as the row that contains the "seed" formatting rule I want to now easily somehow copy/paste down through the rest of the table without having to manually select each cell (by holding down CTRL) in each row and then manually applying the CF like I did in the "seed".

CF example AliGW (1).xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	597	772	218	432	141	83	876	236	392	95	990	873	753	341	770	112
3	402	12	210	115	869	318	62	260	767	125	402	919	733	273	718	430
4	113	181	5	1000	691	275	636	313	485	174	201	371	230	481	984	190
5	194	860	446	523	315	83	538	807	856	190	978	937	959	75	743	916
6	985	246	958	308	145	662	635	806	506	56	573	330	554	557	265	676
7	620	581	101	591	488	838	673	94	106	80	697	556	629	645	827	688
8	740	387	685	554	120	174	503	322	115	585	862	819	842	577	234	451
9	242	7	476	971	935	367	75	512	295	753	549	124	618	340	782	338
10	454	218	212	137	296	131	141	903	703	49	651	790	892	755	116	570
11	845	677	255	869	426	268	434	557	221	485	667	203	187	741	689	118
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2,D2,F2,H2,J2,L2,N2,P2	Other Type	Color scale		NO

Note that the standard "copy/paste" methods I have tried do not result in the desired outcome. It will apply the CF to the correct cells of course, but instead of creating a separate independent version of the same CF rule for each row, it will just add new cells to the range of cells the original "seed" CF applies to, so the one colour gradient CF rule considers ALL the cells, rather than the CF rule just applying to the set of numbers in each row individually.

In other words, if I simply copy/paste the formatting from the original rule to the other rows, Excel adds those new cells to the selection of cells to which the Conditional Formatting applies, rather than copying the new rule and applying it separately to the new row of numbers. This can all be verified in the CF Manager screen when

How do I copy the Conditional Formatting from Row 1 down the table so that new rules are created for each row being formatted, rather than just adding the cells from each row to the original rule?

Thanks

BadSpreadsheet

 

[Peter_SSs]

BadSpreadsheet.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	597	772	218	432	141	83	876	236	392	95	990	873	753	341	770	112
3	402	12	210	115	869	318	62	260	767	125	402	919	733	273	718	430
4	113	181	5	1000	691	275	636	313	485	174	201	371	230	481	984	190
5	194	860	446	523	315	83	538	807	856	190	978	937	959	75	743	916
6	985	246	958	308	145	662	635	806	506	56	573	330	554	557	265	676
7	620	581	101	591	488	838	673	94	106	80	697	556	629	645	827	688
8	740	387	685	554	120	174	503	322	115	585	862	819	842	577	234	451
9	242	7	476	971	935	367	75	512	295	753	549	124	618	340	782	338
10	454	218	212	137	296	131	141	903	703	49	651	790	892	755	116	570
11	845	677	255	869	426	268	434	557	221	485	667	203	187	741	689	118
CF

Welcome to the MrExcel board!

Is this more what you are looking for?
If so, about how many rows is your actual range?

 

[BadSpreadsheet]

BadSpreadsheet.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	597	772	218	432	141	83	876	236	392	95	990	873	753	341	770	112
3	402	12	210	115	869	318	62	260	767	125	402	919	733	273	718	430
4	113	181	5	1000	691	275	636	313	485	174	201	371	230	481	984	190
5	194	860	446	523	315	83	538	807	856	190	978	937	959	75	743	916
6	985	246	958	308	145	662	635	806	506	56	573	330	554	557	265	676
7	620	581	101	591	488	838	673	94	106	80	697	556	629	645	827	688
8	740	387	685	554	120	174	503	322	115	585	862	819	842	577	234	451
9	242	7	476	971	935	367	75	512	295	753	549	124	618	340	782	338
10	454	218	212	137	296	131	141	903	703	49	651	790	892	755	116	570
11	845	677	255	869	426	268	434	557	221	485	667	203	187	741	689	118
CF

Welcome to the MrExcel board!

Is this more what you are looking for?
If so, about how many rows is your actual range?

That is exactly what I want to do!....but not by repeating what I did to create the first row example....ie. not manually clicking on every second cell in each row, applying the CF, then repeating for the next row etc etc etc

How did you apply the CF to each row?

My actual range could have 100's of rows and up to 100 columns.

 

[Peter_SSs]

How did you apply the CF to each row?

I did it like this, but still quite a task if your have hundreds of rows and columns

1. Select B2:Q2
2. Apply color-scale CF. I used the first one in the list:
   
3. Double-click the Format Painter to activate it
4. Individually click on B3, B4, B5, ..., B11 - thus applying the same formatting rules individually to each row.
5. Click Format Painter again to deactivate it
6. Select columns C, E, G, ...., Q by clicking their heading labels (with Ctrl button held after selecting the first column)
7. Conditional Formatting -> Clear Rules -> Clear Rules from Selected Cells

Since you have many more rows and columns, the above process could be accomplished more easily with a macro like this.
I have assumed that there is no Conditional Formatting already in place in the range.

Sub CFSpecial()
  Dim rng As Range, rw As Range
  Dim i As Long
 
  Set rng = Range("B2", Range("B" & Rows.Count).End(xlUp)).Resize(, Cells(2, Columns.Count).End(xlToLeft).Column - 1)
  Application.ScreenUpdating = False
  For Each rw In rng.Rows
      rw.FormatConditions.AddColorScale ColorScaleType:=3
      With rw.FormatConditions(1)
        .ColorScaleCriteria(1).Type = xlConditionValueLowestValue
        .ColorScaleCriteria(1).FormatColor.Color = 7039480
        .ColorScaleCriteria(2).Type = xlConditionValuePercentile
        .ColorScaleCriteria(2).Value = 50
        .ColorScaleCriteria(2).FormatColor.Color = 8711167
        .ColorScaleCriteria(3).Type = xlConditionValueHighestValue
        .ColorScaleCriteria(3).FormatColor.Color = 8109667
      End With
  Next rw
  For i = 2 To rng.Columns.Count Step 2
    rng.Columns(i).FormatConditions.Delete
  Next i
  Application.ScreenUpdating = True
End Sub

 

[BadSpreadsheet]

OK thanks....even that first method works well enough!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

I don't know if that was a one-off job for you and now done, but here is another way where you don't have to do steps 6 & 7 above.
Apply these two CF rules (one color scale and one formula rule) to B2:Q2 making sure that they are showing in the correct order and that 'Stop if True' is checked on the first one.
Then just do the Format Painter thing row-by-row.