Get Cell RGB Color of Excel Cell with VBA Function (1 Viewer)

Status
Not open for further replies.

Rx_

Nothing In Moderation
Local time
Yesterday, 20:36
Joined
Oct 22, 2009
Messages
2,803
A quick lesson for learning VBA wiht Excel that has a practical use.

Background: Instead of MS Access Reports, customized Excel reports can offer the end user more interactive options to data mine the results.
A report may be prototypes with all the amazing code. The end user will often want to determine the highlighted cells colors (highlighed and colored by the VBA code).
An easy way to do this is to have the end-user highlight the cell they wish to have a different color on the Excel spreadsheet and then send it back.

Open the updated Excel spread sheet. On the Menu go to Developer.
(Developer may not be on by default, in this case go to the custom menu and add Developer)
In Developer, click on the Visual Basic - this opens the code window
Use Insert - Module - the VBAProject viewer will show Module1 added.
Open module1 and past this code:
Code:
Option Explicit
Function GetCellRGB(rcell) As String
    Dim sRGB As String
    Dim C As Long
    Dim R As Long
    Dim G As Long
    Dim B As Long
    C = rcell.Interior.Color
    R = C Mod 256
    G = C \ 256 Mod 256
    B = C \ 65536 Mod 256
    GetCellRGB = "R=" & R & ", G=" & G & ", B=" & B
    Debug.Print "Function GetCellRGB has error " & Err.Number
End Function
Save and close the code view and return to the Excel Spreadsheet

Highlight Cell B2
Next to the Formula bar - look for the Fx - click on it
the Insert Function appears. in the "Or select a category" choose User Defined.
User Defined will only appear when there are user defined functions such as the one just entered.
Choose the GetCellRGB in the list
The Function Argument dialogue box appears. Click on one of the cells that have a background color.
At this point, an OK will return the value to Cell B2.
Or, just read the Formula Result below and reclick on several cells.

The user of this in Excel code could be:
1. When returning a Recordset to Excel, save the record count in a variable intMaxRecordCount. The starting location for the data is important. For example if A5 the start of the data, be sure to start i at 5 and add 5 to the record count for the loop. intRowPos = 5

Code:
With objxl.ActiveWorkbook.ActiveSheet ' Loop Excel Rows - find a Facility
          For i = intRowPos To intMaxRecordCount + intRowPos
                If .Cells(i, "R").Value Then   ' IsFacility - a True/Fasle column
                    .Cells(i, "B").Interior.Color = RGB(172, 185, 202) 'light Blue
                    .Cells(i, "R").Interior.Color = RGB(172, 185, 202) 'light Blue
                    '.Range(.Cells(i, "B"), .Cells(i, "D")).Font.FontStyle = "Bold"
                        '.Cells(i, 33).Value = .Cells(i, 3).Value
                        'Else
                        '.Range(.Cells(i, "B"), .Cells(i, "D")).Font.ColorIndex = 16 'metalic gray
                End If
Next i
End With
 
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom