VBA Highlight cell if another cell is Highlighted

[Shelby21]

Hi guys,

Need a bit of help with some formatting. I'm thinking the only way to accomplish what I want is with vba formatting.

Let me explain what the spreadsheet is doing first so you can understand:

In cells B5:E5, user enters values based on different robot hand positions

Cell B5 is the first value entered and cells C5:E5 are compared to B5

Cell I5 calculates the total deviation of C5:E5 compared to B5

If total deviation is greater than 30, I5 turns red and in cells C5:E5, the lowest and highest values compared to B5 are highlighted yellow

In column R, the values entered in B5:E5 are converted by multiplying the values by 0.01 [this is how the values display on our GUI]

Here is what I would like to do:

If I5 is greater than 30, highest and lowest values in cells C5:E5 are highlighted yellow [this is already done]

What I would also like to do is highlight the corresponding values in column R

In the example below, since I5 is greater than 30, C5 and E5 are the highest and lowest values compared to B5 and are highlighted yellow.

How can I also highlight the values in R9 and R17 yellow to correlate with the highlighted values in C5 and E5?

Please keep in mind this should only happen if I5 is greater than 30.

 

[mikerickson]

Why R9 and R17?
The high/low values are 65 and 20, yet neither 65 nor 20 are anywhere in the chart in columns O:R.

How does Excel calculate R9 and R17 from 65 and 20?

 

[Shelby21]

Why R9 and R17?
The high/low values are 65 and 20, yet neither 65 nor 20 are anywhere in the chart in columns O:R.

How does Excel calculate R9 and R17 from 65 and 20?

Hi Mike,

I think you misread the values.

The high/low values are 61 and 20 as shown in cells E5 and C5 respectively.

In cell R9, C5 is multiplied by 0.01 which = 0.20

In cell R17, E5 is multiplied by 0.01 which = 0.61