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:
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
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
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
'.Range(.Cells(i, "B"), .Cells(i, "D")).Font.ColorIndex = 16 'metalic gray