On Tue, 28 Apr 2009 16:09:01 -0700, OssieMac
wrote:
The range for the conditional format is set by the following of code:-
With .AutoFilter.Range
Set rngFilter = .Offset(1) _
.Resize(.Rows.Count - 1)
End With
Column numbers have been left out of the Offset and Resize and hense their
default values are used. (Offset defaults to zero and Resize defaults to
number of columns in AutoFilter.Range.)
Wow, thanks for this neat thread. I'm constantly learning in Excel.
I don't think it'll ever stop <g.
The code could be written as this to include the default values for columns:-
With .AutoFilter.Range
Set rngFilter = .Offset(1, 0) _
.Resize(.Rows.Count - 1, .Columns.Count)
End With
The code as is sets conditional format to the actual data below the column
headers of the filtered range. If your conditional formatting is starting on
B3 instead of A3 then do I assume correctly that column A is not included in
the filtered range? If my assumption is correct, give me an example of the
That's seems to be the case. I didn't notice initially that something
was wrong since my original yellow are the one you used are close
enough that column A still had the colour even if not corect. When I
did notice the difference, I deleted all conditional formatting and
invoked the code column A underneath the header row was left white in
colour.
full range address of the filtered range and then what is the range that you
want the conditional format applied to.
You can change the above code to apply conditional format to other ranges by
changing the Offset and Resize. The following example includes one column to
the left of the Filtered Range:-
With .AutoFilter.Range
Set rngFilter = .Offset(1, -1) _
.Resize(.Rows.Count - 1, .Columns.Count + 1)
End With
Note to include one column to left Offset column is -1 and then Resize needs
1 added to columns otherwise the right column of the filtered range is not
included.
I'll have to play with the above since the written word will not be
clear until I see the results.
Thanks, will give all the above a try. Hopefully I can figure out
what will get A3 to the bottom of column A also included in the
conditional formatting.
You will find the vb color constants under Color Constants in Help. There
are only 8 of them.
Ah, knowing the term will help tremendously!
-----------------------------
Also found a reference online, too:
2009-04-28 19:51:27
(
http://groups.google.ca/group/micros...58b7fdae553401)
Mark,
From: Color Constants in Excel VBA help...
Constant Value
vbBlack 0x0
vbRed 0xFF
vbGreen 0xFF00
vbYellow 0xFFFF
vbBlue 0xFF0000
vbMagenta 0xFF00FF
vbCyan 0xFFFF00
vbWhite 0xFFFFFF
Regards,
Jim Cone
San Francisco, CA
-----------------------------
RGB function actually returns a number that represents the color. The
following code is an example of finding the actual number that a color
returns and it can be used in lieu of the RGB function. Just use the color
palette to set the interior color of a cell in a worksheet to your preferred
color, ensure that it is the active cell and run the following code to return
the number.
Sub FindcolorCode()
MsgBox ActiveCell.Interior.Color
End Sub
Wow, thanks for this! I'm putting this in my PERSONAL.XLS immediately
Do you know what will send the value to the clipboard at the same
time?
Your RGB color should return 13434879. You can then set the color in VBA as
per the following code:-
.FormatConditions(1).Interior.Color = 13434879
Just for interest, the maths behind the RGB function is as in the following
code:-
Sub RGB_Maths()
Dim lngR as Long 'Red
Dim lngG As Long 'Green
Dim lngB As Long 'Blue
Dim RGBValue As Long
lngR = 255
lngG = 255
lngB = 204
RGBValue = lngR + lngG * 2 ^ 8 + lngB * 2 ^ 16
MsgBox RGBValue
End Sub
Thanks once again! Much appreciated. :oD