Macro to get the colour of a particular cell [Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I hope somebody can assist me with this. I wanted a macro which identifies the colour of the cell and displays the colour in a notepad for first ten rows and 20 columns of the excel.


2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
From what I know, the color are basically number. So if you are looking for RED, green etc, that I am not sure is possible.
yea rizvisa ... but how can I detect that .. any idea ..?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
You did not mention how the file should be


You have to use

Cells.Interior.color property to get background color

and

cells.font.color to get font color



Here are the two functions that will help

1. To find font color as long in cell A2
=ShowFontColor(A2)

2. To find font color as RGB in cell A2
=ShowFontColor(A2, true)


3. To find background color as long in cell A2
=ShowBackgroundColor(A2)

4. To find background color as RGB in cell A2
=ShowBackgroundColor(A2, true)

Function ShowFontColor(Cell As Range, Optional bShowRGBValue As Boolean = False) As Variant
Dim lColorIndex As Long
Dim sColor As Variant

    If Cell.Count <> 1 Then
        MsgBox "You can have only one cell"
        ShowFontColor = "Error: Too man Cells"
        Exit Function
    End If
    
    lColorIndex = Cell.Font.Color
    
    If (bShowRGBValue) Then
    
        sColor = "RGB("
        sColor = sColor & lColorIndex Mod 256
        
        sColor = sColor & ","
        sColor = sColor & (lColorIndex \ 256) Mod 256
        
        sColor = sColor & ","
        sColor = sColor & (lColorIndex \ 256 \ 256) Mod 256
        
        sColor = sColor & ")"
    Else
    
        sColor = lColorIndex
    
    End If
    
    ShowFontColor = sColor
    
End Function


Function ShowBackgroundColor(Cell As Range, Optional bShowRGBValue As Boolean = False) As Variant
Dim lColorIndex As Long
Dim sColor As Variant

    If Cell.Count <> 1 Then
        MsgBox "You can have only one cell"
        ShowBackgroundColor = "Error: Too man Cells"
        Exit Function
    End If
    
    lColorIndex = Cell.Interior.Color
    
    If (bShowRGBValue) Then
    
        sColor = "RGB("
        sColor = sColor & lColorIndex Mod 256
        
        sColor = sColor & ","
        sColor = sColor & (lColorIndex \ 256) Mod 256
        
        sColor = sColor & ","
        sColor = sColor & (lColorIndex \ 256 \ 256) Mod 256
        
        sColor = sColor & ")"
    Else
    
        sColor = lColorIndex
    
    End If
    
    ShowBackgroundColor = sColor
    
End Function

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!