VBA to find and replace RGB scheme

[excelsishya]

Hi all,

I am in search of VBA code which can find and replace color scheme in active tab.

Sheet2

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 80px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td style="background-color: rgb(0, 128, 128);">HEADER</td> <td style="background-color: rgb(0, 128, 128);">
</td> <td style="background-color: rgb(0, 128, 128);">
</td> <td style="background-color: rgb(0, 128, 128);">
</td> <td style="background-color: rgb(0, 128, 128);">
</td> <td style="background-color: rgb(0, 128, 128);">
</td> <td style="background-color: rgb(0, 128, 128);">
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td style="color: rgb(51, 51, 153);">SUBHEADER</td> <td style="color: rgb(51, 51, 153);">
</td> <td style="color: rgb(51, 51, 153);">
</td> <td style="color: rgb(51, 51, 153);">
</td> <td style="color: rgb(51, 51, 153);">
</td> <td style="color: rgb(51, 51, 153);">
</td> <td style="color: rgb(51, 51, 153);">
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="color: rgb(153, 204, 0);">HEADING</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td>A</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td>B</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td>C</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td>TOTAL</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr></tbody></table>
In above header fill is 1 color , there are 2 sub heading of different color , further there contents,

entire table has border which is same as header color.

is it possible using VBA to identify above color in action,replace at once with different color,

eg header green = color 1(this should change both fill as well as border)

subheader color = colour2(font)

I am using excel 2007.

Thanks

 

[excelsishya]

Just follow up on above,

Would it make easier to achieve above in VBA if we know which are RGB in use.

Say i know 3 RGB are in use, 1 in table heading border, another in subheading and header2.

I am looking for way replace my old financial models (multiple tabs ) created in specific color schemes into new.

Thanks

 

[kpark91]

Try something like this.
http://www.mrexcel.com/forum/showthread.php?t=24681

More information in how colors are handled is available in this website:
http://www.wilsonmar.com/1colors.htm

 

[excelsishya]

Kpark91, thanks for link.i followed link you gave .

I used code in the link to find RGB in use when i used by typing the function name , UDF appears when try to it enter giving cell reference filled with color it showing syntax error.

Could please suggest am i missing any step in execute it.

and also this code is only for finding RGB . is it possible in VBA to replace existing color scheme with new.


Below is code which i used in excel2007.

Function showRGB2(rcell)

Dim myStr As String

Application.Volatile

myStr = Right("000000" & Hex(rcell.Interior.Color), 6)

showRGB2 = Application.Evaluate("=Hex2dec(""" & Right(myStr, 2) & """)") & _

", " & Application.Evaluate("=Hex2dec(""" & Mid(myStr, 3, 2) & """)") & ", " _

& Application.Evaluate("=Hex2dec(""" & Left(myStr, 2) & """)")

End Function