VBA Highlight cell if another cell is Highlighted

Shelby21

Board Regular
Joined
Nov 21, 2017
Messages
51
Office Version
  1. 365
Platform
  1. Windows
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.


cr.PNG
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,332
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?
 
Upvote 0

Shelby21

Board Regular
Joined
Nov 21, 2017
Messages
51
Office Version
  1. 365
Platform
  1. Windows
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
 
Upvote 0

Forum statistics

Threads
1,186,846
Messages
5,960,169
Members
438,463
Latest member
netobraga

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