Cell color update automatic

ebea

Board Regular
Joined
Jul 12, 2008
Messages
215
Office Version
  1. 2010
Platform
  1. Windows
Hi! I have the following code, which works well, if I manually change the RGB vaues in the respective RGB cells. But as the RGB values are updated by a formula, the color cells linked, are not updated when RGB values change.
How can I get it to update colors, when formulas change the RGB values?
Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Intersect(Target, Range("E16:F46")) Is Nothing Then Exit Sub
With Range("B" & Target.Row)
.Interior.Color = RGB(.Offset(, -3).Value, .Offset(, -2).Value, .Offset(, -1).Value)
End With
End Sub
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

ebea

Board Regular
Joined
Jul 12, 2008
Messages
215
Office Version
  1. 2010
Platform
  1. Windows
Use the Worksheet_Calculate event instead?
I have tested that possibility, but with same result; no changing in Colors. It works only, if I manually update the RGB (just one number is enough), OR if I Copy the RGB numbers, and Paste as Values. So for now, I run out of ideas.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
You said that the RGB numbers are changed by a formula. Now you are saying that you can manually update, or paste the values in. And you wonder why Worksheet_Calculate doesn't trigger? If you are messing with the values as you describe, put a dummy formula that, say, sums all the RGBs, so that any change to any value triggers a recalculation ... and should trigger the event?
 

ebea

Board Regular
Joined
Jul 12, 2008
Messages
215
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

You said that the RGB numbers are changed by a formula. Now you are saying that you can manually update, or paste the values in. And you wonder why Worksheet_Calculate doesn't trigger? If you are messing with the values as you describe, put a dummy formula that, say, sums all the RGBs, so that any change to any value triggers a recalculation ... and should trigger the event?
I can Copy the RGB numbers, and insert these as Values, in another place on Sheet, and then the Color changes. OR, if I manually change a number, colors also change. But the Formula update of RGB numbers, do not trigger to update Colors. And thats my problem.I have tried, to put in a formula, on another place in sheet, which says = to RGB this or that numbers, without any luck. Formulas do not trigger the color to update.
The whole idea is, that a specific number from another cell, update the RGB values, and then the RGB values should update the colors.
 

ebea

Board Regular
Joined
Jul 12, 2008
Messages
215
Office Version
  1. 2010
Platform
  1. Windows
I can Copy the RGB numbers, and insert these as Values, in another place on Sheet, and then the Color changes. OR, if I manually change a number, colors also change. But the Formula update of RGB numbers, do not trigger to update Colors. And thats my problem.I have tried, to put in a formula, on another place in sheet, which says = to RGB this or that numbers, without any luck. Formulas do not trigger the color to update.
The whole idea is, that a specific number from another cell, update the RGB values, and then the RGB values should update the colors.
colorchange.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2RGBRGB
3101580001330
4402350101580
55712550201840
6402350302090
7101580402350
81015805712550
9
10Numbers in Column B, are updated from another sheet, with numbers
11And these numbers, get the RGB numbers in Column E, F, G to change,
12according to the reference in Columns X, Y, Z
13Column W are the numbers, which gives the Color reference.
14
Ark1
Cell Formulas
RangeFormula
E3:E8E3=INDEX($X$3:$X$33,MATCH(B3,$W$3:$W$33,0),1)
F3:F8F3=INDEX($Y$3:$Y$33,MATCH(B3,$W$3:$W$33,0),1)
G3:G8G3=INDEX($Z$3:$Z$33,MATCH(B3,$W$3:$W$33,0),1)


Maybe this little example, can give an idea of what it is. Column B, as you see, has change the Color, because the number inside has changed. But Color only change, because I manually change RGB numbers in Column E, F or G. The code do not change it, nor do the Formula.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
I've just said that formula results trigger the Worksheet_Calculate event ... and you replied that that event doesn't trigger when doing things manually ... and I gave the solution for that ... and now you've gone back to the beginning again. What event are you using now, exactly?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,347
Messages
5,624,136
Members
416,012
Latest member
rockermom59

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