Cell color update automatic

[ebea]

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

 

[GlennUK]

Use the Worksheet_Calculate event instead?

 

[ebea]

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]

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]

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2					R	G	B																	R	G	B
3		1			0	158	0																0	0	133	0
4		4			0	235	0																1	0	158	0
5		5			71	255	0																2	0	184	0
6		4			0	235	0																3	0	209	0
7		1			0	158	0																4	0	235	0
8		1			0	158	0																5	71	255	0
9
10				Numbers in Column B, are updated from another sheet, with numbers
11				And these numbers, get the RGB numbers in Column E, F, G to change,
12				according to the reference in Columns X, Y, Z
13				Column W are the numbers, which gives the Color reference.
14
Ark1

Cell Formulas
Range	Formula
E3:E8	E3	=INDEX($X$3:$X$33,MATCH(B3,$W$3:$W$33,0),1)
F3:F8	F3	=INDEX($Y$3:$Y$33,MATCH(B3,$W$3:$W$33,0),1)
G3:G8	G3	=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]

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?