Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Closed Thread
 
Thread Tools Rate Thread Display Modes
Old 05-04-2015, 08:44 AM   #1
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,800
Thanks: 636
Thanked 337 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Lightbulb Get Cell RGB Color of Excel Cell with VBA Function

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

__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!
Rx_ is offline  
The Following 2 Users Say Thank You to Rx_ For This Useful Post:
hassanogaibi (06-28-2016), Uncle Gizmo (05-04-2015)
Closed Thread

Tags
color function , excel , rgb , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Excel - Count cells or Sum cell's values by background Color Rx_ Code Repository 1 09-12-2014 12:05 AM
Interpreting Excel cell color geoB Modules & VBA 3 11-26-2008 01:40 PM
Create excel file from Access and enter 'Round' function in a cell c_breemersch Modules & VBA 2 11-17-2008 05:05 AM
How to create Excel sheet cell by cell accesser2003 General 1 09-05-2007 01:55 PM
Import from Excel - cell by cell ?? RTC General 12 11-17-2003 02:05 AM




All times are GMT -8. The time now is 11:06 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World