Macro to get the colour of a particular cell

Closed
rac - Jun 7, 2010 at 06:59 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 7, 2010 at 09:37 AM
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 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 7, 2010 at 07:12 AM
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 ..?
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 7, 2010 at 09:37 AM
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
0