make UDF behave like other functions

[Shaun]

Hi Guys,

I am trying to get the RGB values of a cell and I have found a function which does that here.

Function Color(rng As Range, Optional formatType As Integer = 0) As Variant
Application.Volatile
  Dim colorVal As Variant
  colorVal = Cells(rng.Row, rng.Column).Interior.Color
  Select Case formatType
  Case 1
  Color = Hex(colorVal)
  Case 2
  Color = (colorVal Mod 256) & ", " & ((colorVal \ 256) Mod 256) & ", " & (colorVal \ 65536)
  Case 3
  Color = Cells(rng.Row, rng.Column).Interior.ColorIndex
  Case Else
  Color = colorVal
  End Select
End Function

But what it does not do is update the RGB values when the cell fill is changed, like it would if the values in A1 and B1 were changed in the formula:

=A1+B1

I have tried adding:

Application.Volatile

however this only seems to update the function on opening the function. I have also made sure that the calculation setting is set to automatic.

Is there a way to make the function recalculate when the user changes the fill colour?

Regards,

Shaun

 

[Hui]

A volatile function will be recalculated whenever calculation occurs in any cells on the worksheet.
Changing color isn't a calculation

There are work arounds so have a read of:
https://sites.google.com/site/e90e50fx/home/sum-by-color-calculate-when-color-changed

 

[Shaun]

Good afternoon Hui,

Thank you for your reply and the link.

I will look into it.

Cheers

Shaun

 

[Shaun]

Hi All,

Just some follow up with respect to the UDF and more importantly what I was trying to achieve with that UDF.

What was I trying to do: Use the above UDF to get the RGB values (in the string format xxx, xxx, xxx) of a cell and use in other subs.

This was never going to work, as I found out.

When changing a cell's fill colour in something like:

ActiveSheet.Range("$F$5").Interior.Color = RGB(112, 48, 160)

I was trying to use a string generated by Case 2 in the above UDF in this manner:

ActiveSheet.Range("$F$5").Interior.Color = RGB(ActiveSheet.range("$F$1").Value)

I was expecting this to resolved as:

ActiveSheet.Range("$F$5").Interior.Color = RGB(112, 48, 160)

What I did not realise is that Excel is looking for three integers between 0 and 255 not a string representing three integers.

The fix:

Sub Colour()
'
' Calculate the RGB values
'
Dim Col As long
Dim R As Long
Dim G As Long
Dim B As Long

Col = Activesheet.Range("$A$1").Interior.Color 'Get the cell fill colour of cell
R = Col Mod 256
G = (Col \ 256) Mod 256
B = Col \ 65536

and using this to provide the integers required to make it all work:

ActiveSheet.Range("$F$5").Interior.Color = RGB(R, G, B)

This may be old ground, but I hope this helps someone trying to do something similar.

Attached is an example.

Cheers

Shaun

 

[Marc L]

Hi !

Why don't you use ColorIndex property instead ?

Like [F5].Interior.ColorIndex = [A1].Interior.ColorIndex …

Works also with [F5].Interior.Color = [A1].Interior.Color
no need any RGB conversion …
​

 

[Shaun]

Hi Marc L

Because I had no idea it existed.

In the example I used cell fill colours but I am actually using it for broken out chart properties.

Would your method work in charts where the RGB colour value was taken from a cell interior.color and applied to a pivot chart in something like Format.Line.Forecolor.RGB =?

Cheers

Shaun

 

[Marc L]

1. VBA inner help !

2. Yes as you can see in sample in VBA inner help of RGB property !
​

 

[NARAYANK991]

Hi Shaun ,

The RGB values need not be resolved to fill the interior of a cell with any desired color.

ActiveCell.Interior.Color = RGB(112, 48, 160)

is the same as :

ActiveCell.Interior.Color = 10498160

where the combined value of Red (112) , Green(48) and Blue(160) equals the Long value 10498160.

112 + 48*256 + 160*65536 = 10498160

Narayan