CODEWITHSUNDEEP
excelvba
Shawn V. Wilson
Shawn V. Wilson

Reputation: 1111

Detect the Conditional color of a cell

I'm doing a mail merge from Excel into Word, and I need to copy the background colors of some cells as well as the contents.

Here on SO I've learned that I could add a helper column, and insert a custom function (something like Selection.Interior.Color) that detects the code of the desired cell. I was going to have the mail merge stealthfully pass that code to Word, where a macro would see it and use it to colorize the corresponding table cell.

Unfortunately, Selection.Interior.Color only detects the natural, underlying color of the cell, not the conditionally-formatted color.

Is there a way to detect the color as assigned by the conditional formatting?

(There are 35 different columns using at least 8 different sets of conditional rules).

CONCLUSION: These solutions seem to work, but I decided to avoid adding data or macros to the Excel sheet. Instead I put a macro in Word that basically duplicates the Conditional Formatting functionality. It's slow, but I think its ultimately cleaner.

Thanks, everyone.

Upvotes: 0

Views: 2119

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

Here is a routine which places the displayformat.interior.color code in a "helper column" next to the column being tested. I also added a column to show the RGB values, but only for interest. Oh, and the colors were all generated by conditional format.

Option Explicit
Sub GetColor()
    Dim R As Range, C As Range
Set R = Range(Cells(2, 1), Cells(10, 1))
For Each C In R
    C.Offset(0, 1).Value = C.DisplayFormat.Interior.Color
    C.Offset(0, 2).Value = converttorgb(C.Offset(0, 1).Value)
Next C
End Sub

Function ConvertToRGB(lColor As Long) As String
    Dim H As String
    Dim Red As Integer, Green As Integer, Blue As Integer
    H = Format(Hex(lColor), "@@@@@@")
Red = Val("&H" & Right(H, 2))
Green = Val("&H" & Mid(H, 3, 2))
Blue = Val("&H" & Left(H, 2))

ConvertToRGB = Format(Red, "0\, ") & Format(Green, "0\, ") & Format(Blue, "0")

End Function

enter image description here

Depending on how you are transferring this information to Word, you may not even need to have it on the worksheet.

Upvotes: 1

CMArg
CMArg

Reputation: 1567

As @David said, it seems to be a pain. However, if the conditional formatting includes only the "traditional" standard excel colors (see here), the following seems to respond properly (not exhaustively tested). Column A (rows 1 to 12) contains values from 1 to 12, and conditional formatting were applied to those cells. The code below seems to work, as long as colors are "standard".

Sub Button1_Click()
    For i = 1 To 12
        Worksheets("Sheet1").Cells(i, 2) = Worksheets("Sheet1").Cells(i, 1).DisplayFormat.Interior.ColorIndex
    Next
End Sub

Upvotes: 1

Related Questions