Convert font index into string

HuaMin Chen
HuaMin Chen used Ask the Experts™
on
Dear,
Is there one example to convert Font.Color of a VBA range, into string?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
something like this?

Sub test()
    Dim r As Range, tmp As String
    Set r = Range("A1", "E2")
    For Each c In r
        tmp = "" & c.Font.Color
        Debug.Print "Cell (" & c.Address & ") font color = " & tmp
    Next
End Sub

Open in new window

HuaMin ChenProblem resolver

Author

Commented:
I tried both these
        Debug.Print "" & CStr(Worksheets("BK-ACER_c").Cells(RowID, 1).Font.ColorIndex)
        Debug.Print "" & Worksheets("BK-ACER_c").Cells(RowID, 1).Font.Color

Open in new window


but it does only display either 1 or 0 (having no difference to different rows), even if there are some rows in yellow and some are not
Ryan ChongSoftware Team Lead

Commented:
but it does only display either 1 or 0 (having no difference to different rows), even if there are some rows in yellow and some are not

are you checking font color or background color ??
HuaMin ChenProblem resolver

Author

Commented:
Background color. I will use Interior.Color
Software Team Lead
Commented:
so background color is not font color, try this instead:

Sub test()
    Dim r As Range, tmp As String
    Set r = Range("A1", "E2")
    For Each c In r
        tmp = "" & c.Font.Color
        Debug.Print "Cell (" & c.Address & ") font color = " & tmp
        
        tmp = "" & c.Interior.Color
        Debug.Print "Cell (" & c.Address & ") background color = " & tmp
    Next
End Sub

Open in new window


Refers to Interior.Color instead.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial