JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,546
- Office Version
- 365
- Platform
- Windows
I need to change the fill color for a column of cells based on RBG values. For example:
<tbody>
</tbody>
In D2:D5, I'd like to set the fill color based on the RGB values in the cells in A:C of that row.
I couldn't find a worksheet function, so I wrote a UDF based on code from the macro recorder.
Here's the recorder code:
If I run it from the macro list, it works, but only on D2 and only for that one color. So I tried to adapt it. I got this, which gets a #VALUE error on the .Pattern statement.
Is there a way to make this function work?
PS: how come VBA changes the first character of all of the attributes except for .color to upper case?
R/C | A | B | C | D |
1 | R | G | B | Color |
2 | 255 | 0 | 0 | |
3 | 0 | 255 | 0 | |
4 | 0 | 0 | 255 | |
5 | 85 | 170 | 255 |
<tbody>
</tbody>
In D2:D5, I'd like to set the fill color based on the RGB values in the cells in A:C of that row.
I couldn't find a worksheet function, so I wrote a UDF based on code from the macro recorder.
Here's the recorder code:
Code:
Sub Macro1()
Range("D2").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.color = 5614335
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub
Code:
Function SetRGB(inTarget As Range, R As Byte, G As Byte, B As Byte)
inTarget.Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.color = RGB(R, G, B)
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Function
PS: how come VBA changes the first character of all of the attributes except for .color to upper case?