Apply the same conditional formatting to another cell when it's applied to others

[Glasgowsmile]

I have this formula for Conditional formatting

=ROUND($D$3*$C7,)>D7

and what I want to do... If the formatting is applied to any cell in range D7:NE41, then I want to apply that same formatting to D45:NE79 - the issue is that if I add that second range to the "Applies to" section of the conditional formatting, it doesn't work likely due to the range in the rule but data only exists up to row 41, so I assume that's why the rule doesn't work. I could create a helper column with the same data but I'm trying to avoid that.

So, I'm looking for a workaround.

My thought right now is below but it's not working, says Object required and I'm not sure what the issue is.

Sub Colors()
If ThisWorksheet.Range("D7:NE41").Interior.Color = &HDA9694 Then
    ThisWorksheet.Range("D45:NE79").Interior.Color = &HDA9694
End If
End Sub

 

[Joe4]

How about applying the Conditional Formatting Rule to the range D7:NE79, but updating your rule to this:

=AND(ROUND($D$3*$C7,)>D7,OR(ROW()<=41,ROW()>=45))

 

[Glasgowsmile]

How about applying the Conditional Formatting Rule to the range D7:NE79, but updating your rule to this:

=AND(ROUND($D$3*$C7,)>D7,OR(ROW()<=41,ROW()>=45))

This still worked for the top section but the bottom section D45:NE79 didn't change.

When I applied I7 was highlighted but I expected I45 to also be highlighted and it wasn't.

 

[Joe4]

Is the lower section still supposed to work off of D3, or some other cell?

Can you tell me what is in the following cells?
D3
C45
I45

 

[Glasgowsmile]

Is the lower section still supposed to work off of D3, or some other cell?

Can you tell me what is in the following cells?
D3
C45
I45

Correct, I want the lower section to work off the same cells as the top.

D3 is a % value, that needs to be the same each time.

C45 is nothing, so it shouldn't be referenced. I45 is a value but if I7 for example is highlighted then so should I45. Anything from D7:NE7 that's highlighted should also impact D45:NE45, and the same goes for the rest of the rows through the range.

 

[Joe4]

If the Conditional Formatting formula for cell D7 is:

=ROUND($D$3*$C7,)>D7

then the exact same formula applied to cell D45 (based on your use of absolute/relative range references) would be:

=ROUND($D$3*$C45,)>D45

If that is not what you want, then either your formula is incorrect, or you have not explained your question clearly.

You said that you wanted to extend this out to other rows and other columns.
Perhaps it will clarify things a bit if you tell us what the formula should be for these other cells in your defined range:
F7:
D8:
F45:
D46:

Let us know what the Conditional Formatting for those 4 cells should look like, and then I think it will be clearer exactly what you are trying to do, and needs adjusting in your formula.

 

[Glasgowsmile]

If the Conditional Formatting formula for cell D7 is:

=ROUND($D$3*$C7,)>D7

then the exact same formula applied to cell D45 (based on your use of absolute/relative range references) would be:

=ROUND($D$3*$C45,)>D45

If that is not what you want, then either your formula is incorrect, or you have not explained your question clearly.

You said that you wanted to extend this out to other rows and other columns.
Perhaps it will clarify things a bit if you tell us what the formula should be for these other cells in your defined range:
F7:
D8:
F45:
D46:

Let us know what the Conditional Formatting for those 4 cells should look like, and then I think it will be clearer exactly what you are trying to do, and needs adjusting in your formula.

Yeah, I'm having a hard time explaining this issue.

I need to compare it to the D7:NE41 only, changing it to C45 > D45 won't work because the values are different but those rows still correlate with the above rows so I want them also highlighted.

If D7 for example is highlighted, then D45 needs to be highlighted because these two cells are related but I cannot highlight D45 by using that cell in the conditional format because the values in that cell are different, so it wouldn't work.

 

[Joe4]

I really don't understand what you are trying to say. I think it would be much better if you could show us an actual example of each section (so we can see that data), and explain what you want to happen and why (explaining in the terms of the real data shown, not just generalities).

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Glasgowsmile]

I really don't understand what you are trying to say. I think it would be much better if you could show us an actual example of each section (so we can see that data), and explain what you want to happen and why (explaining in the terms of the real data shown, not just generalities).

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Let's try this:

This is basically the same scenario I have. The top gets highlighted based on the values in B3:B6, $C$2, and the values in C3:D6.

You can see at the bottom they're also labeled A-D, they're related to the top part and I want those values to be highlighted too if the corresponding cell at the top is also highlighted but I don't know how to do that. I don't want to compare B3:B6 against C9:D12.

Book1
	A	B	C	D	E
1	Threshold		40%
2
3	A	10	8	7
4	B	12	7	8
5	C	14	4	3
6	D	8	1	3
7
8
9	A		$4	$3
10	B		$6	$2
11	C		$7	$9
12	D		$10	$3
13
14
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:D6	Expression	=ROUND($C$1*$B3,)>C3	text	NO

 

[Joe4]

OK, I see what you mean now (pictures really do "say 1000 words!").

This is actually a lot simpler than you think.
Simply highlight your lower section, and use the EXACT same Conditional Formatting color.

So, for the example above, you would simply select range C9:D12, and enter the Conditional Formatting formula:

=ROUND($C$1*$B3,)>C3

Make sense?