X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Must I Use Rgb To Assign A Conditional Formatting Rule With VBA?, Office 2013    
post Mar 13 2018, 09:42 AM

Posts: 18,324
Joined: 29-March 05
From: Wisconsin

NOTE: Office 2016, not 2013.

I'm using Automation to create an Excel file from within a database. I'm putting the final bit of polish on it at this point. I'm creating a Conditional Formatting rule, and I seem to be required to use RGB for this bit:

    With ws.Cells(intRow, intVerificationCol + intElvaloyCount + 1).FormatConditions.Add(xlExpression, _
                Formula1:="=ABS(" & ColLet(intVerificationCol + intElvaloyCount + 1) & intRow & "/E" & intRow & ")>0.005")
        .Interior.Color = RGB(250, 128, 114) 'Dusty rose - can't seem to use 16777215 in this context for some reason
        .StopIfTrue = False
    End With

This doesn't throw an error, but the Conditional Formatting rule comes out just formatting the cell with a white background, which is indistinguishable from no background.
        .Interior.Color = 16777215

This throws a Subscript out of range (9) error:
        .Interior.ColorIndex = 16777215

The only reason I ask is because I like to manually color a cell, then use the Immediate Window to see the interior color number value, and that doesn't return the RGB values.

Don't we have some sort of utility where you can enter the long integer value and get back the RGB values? (The ones I'm using above are close, but not exact.)

Go to the top of the page
Doug Steele
post Mar 13 2018, 10:31 AM

UtterAccess VIP
Posts: 22,000
Joined: 8-January 07
From: St. Catharines, ON (Canada)

If you look at the Immediate Window, you'll see that the RGB function doesn't return the value you're using:

?RGB(250, 128, 114)

Go to the top of the page
post Mar 13 2018, 10:33 AM

UtterAccess VIP
Posts: 10,614
Joined: 6-December 03
From: Telegraph Hill

Here are some useful functions you can keep in a module, Dennis.

It's really annoying how MS introduced this half-baked functionality - just a couple of built-in functions to go from one type to the other would have been all that was required. frown.gif

Oops! Adding the link might be helpful!

Go to the top of the page
post Mar 13 2018, 01:04 PM

Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Yeah, I ended up having to eyeball the color by looking at the color pallete as I adjusted stuff. I meant to include a note about how I know they don't match perfectly. Bottom line was that I didn't want it to be <Screaming Sam Kinison voice>REDDDDDD!!</sskv>


Thanks for the link! I'm just sure that someone posted a demo to the Code Archive where you could enter the Long Integer value and see the RGB equivalent (among other things), but that linked code will do!

Go to the top of the page
post Mar 13 2018, 02:37 PM

Utter Access VIP
Posts: 6,245
Joined: 22-June 04
From: North Carolina

This is what I use
R = IColor Mod 256
G = IColor \ 256 Mod 256
B = IColor \ 65536 Mod 256

Where IColor is the interior color of the cell.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    12th December 2018 - 10:06 AM