vba overlapping conditional formatting
Susan J-P wrote:
Thanks for the replies. It looks like recording a macro while using Excel's
conditional formatting only generates coding for the formatting I'm after, so
I guess I didn't frame my question particularly well.
The coding I'm struggling with has to do with the VBA logic and application
of the formats. I didn't include any of my VBA in the original post because I
suspect I'm pretty far off target. Here are a few of the things I've tried so
far:
Example 1:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 22 And Target.Column < 23 And Target.Column < 15
Then Exit Sub 'Column O is 15, Column V is 22, Column W is 23
If Target.Row = 1 Then Exit Sub
'
Application.EnableEvents = False
If Target.Column = 22 Then
If Target.Offset(0, 0) = "Internal" And Target.Offset(0, 1) =
"Internal" Then Target.EntireRow.Interior.ColorIndex = 40 Else
Target.EntireRow.Interior.ColorIndex = x1colorindexautomatic
End If
If Target.Column = 23 Then
If Target.Offset(0, 0) = "Internal" And Target.Offset(0, -1) =
"Internal" Then Target.EntireRow.Interior.ColorIndex = 40 Else
Target.EntireRow.Interior.ColorIndex = x1colorindexautomatic
End If
If Target.Column = 15 Then
If Target.Offset(0, 0) = 1 And Target.Offset(0, -1) = "" Then
Target.EntireRow.Font.ColorIndex = 10 Else Target.EntireRow.Font.ColorIndex =
x1colorindexautomatic
If Target.Offset(0, 0) = "RET" And Target.Offset(0, -1) = "RET" Then
Target.EntireRow.Font.ColorIndex = 15 Else Target.EntireRow.Font.ColorIndex =
x1colorindexautomatic
If Target.Offset(0, 0) = "RET" And Target.Offset(0, -1) = 1 Then
Target.EntireRow.Font.ColorIndex = 3 Else Target.EntireRow.Font.ColorIndex =
x1colorindexautomatic
End If
Application.EnableEvents = True
End Sub
Example 2:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngControl As Range
Dim rngVisibility As Range
Dim rngIT18 As Range
Dim rngIT19 As Range
Dim rngExposure As Range
Set rngControl = Target.Column("V")
Set rngVisibility = Target.Column("W")
Set rngIT18 = Target.Column("N")
Set rngIT19 = Target.Column("O")
Set rngExposure = Target.Column("T")
If rngControl = "Internal" And rngVisibility = "Internal" Then
Target.EntireRow.Interior.ColorIndex = 40 Else
Target.EntireRow.Interior.ColorIndex = x1colorindexautomatic
If rngIT18 = "RET" And rngIT19 = "RET" Then
Target.EntireRow.Font.ColorIndex = 48 Else Target.EntireRow.Font.ColorIndex =
x1colorindexautomatic
If rngIT18 = "" And rngIT19 = "RET" Then
Target.EntireRow.Font.ColorIndex = 10 Else Target.EntireRow.Font.ColorIndex =
x1colorindexautomatic
If rngIT18 = 1 And rngIT19 = "RET" Then
Target.EntireRow.Font.ColorIndex = 3 Else Target.EntireRow.Font.ColorIndex =
x1colorindexautomatic
Application.EnableEvents = True
End Sub
--
Susan
Technical Writer
"Bob Phillips" wrote:
That suggests to me that you want more than 3 conditions for the rows, which
is beyond conditional formatting's current capability.
I would suggest you play with CF in excel and work out what can and can't be
done, and once you are happy with it. repeat the process with the macro
recorder turned on. That will give you the basis if your code, you can post
back with particulars if you then have problems.
--
HTH
Bob Phillips
(remove xxx from email address if mailing direct)
"Susan J-P" wrote in message
...
Firstly, I'd like to stress that I'm a VBA rookie, so I'd appreciate
replies
will with details and explanations.
I'm trying to figure out how to build some VBA conditional formatting to:
1. test multiple cell criteria
2. overlap (when needed) the resulting conditional formatting
For example, in my worksheet, I want to test the content of columns N and
O
for 3 different possible conditions and format the entire row accordingly:
if N is blank and O is a number, use a green font for the entire row
if N is "RET" and O is "RET", use a grey font for the entire row
if N is a number and O is "RET", use a red font for the entire row
Plus, I want to test the contents of columns V and W for a single
condition
and format the entire row accordingly:
if V is "Internal" and W is "Internal", use a peach background for the
entire row
This latter test should be able to overlap with any of the first set.
And, lastly, I want to test the contents of column T for a single
condition
and format the entire row accordingly:
if T <1 and the row above =1, then draw a heavy blue line above the
entire row
And this third also should be able to overlap with any of the prior sets.
Finally, when any of these conditions change (so the criteria are no
longer
met), I need the formatting to revert back to the default.
I know this will need to be triggered by a Worksheet_Change event, but
have
been fiddling for a couple of days without much luck.
--
Susan
Technical Writer
This should work and do what you wanted to do
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Static myoldcell
If myoldcell = Empty Then
myoldcell = ActiveCell.Address
End If
If Range(myoldcell).Row = 1 Or Range(myoldcell).Column = 1 Or
ActiveCell.Count 1 Then
Exit Sub
End If
If Application.IsNumber(Range(myoldcell).Offset(0, 1)) = True And
Range(myoldcell) = "" And Range(myoldcell).Column = 14 Then
Range(myoldcell).EntireRow.Font.Color = RGB(50, 140, 70)
Else
Range(myoldcell).EntireRow.Font.ColorIndex = 1
End If
If Application.IsNumber(Range(myoldcell)) = True And
Range(myoldcell).Offset(0, -1) = "" And Range(myoldcell).Column = 15
Then
Range(myoldcell).EntireRow.Font.Color = RGB(50, 140, 70)
End If
If Application.IsNumber(Range(myoldcell).Offset(0, 1)) = False And
Range(myoldcell) = "" And Range(myoldcell).Column = 14 Then
Range(myoldcell).EntireRow.Font.ColorIndex = 1
End If
If Application.IsNumber(Range(myoldcell)) = False And
Range(myoldcell).Offset(0, -1) = "" And Range(myoldcell).Column = 15
Then
Range(myoldcell).EntireRow.Font.ColorIndex = 1
End If
If Range(myoldcell) = "RET" And Range(myoldcell).Offset(0, 1) = "RET"
And Range(myoldcell).Column = 14 Then
Range(myoldcell).EntireRow.Font.Color = RGB(130, 130, 130)
'Else: Range(myoldcell).EntireRow.Font.ColorIndex = 1
End If
If Range(myoldcell) = "RET" And Range(myoldcell).Offset(0, -1) = "RET"
And Range(myoldcell).Column = 15 Then
Range(myoldcell).EntireRow.Font.Color = RGB(130, 130, 130)
'Else: Range(myoldcell).EntireRow.Font.ColorIndex = 1
End If
If IsNumeric(Range(myoldcell)) = True And Range(myoldcell).Offset(0, 1)
= "RET" And Range(myoldcell).Column = 14 Then
Range(myoldcell).EntireRow.Font.Color = RGB(200, 20, 10)
End If
If Range(myoldcell) = "RET" And IsNumeric(Range(myoldcell).Offset(0,
-1)) = True And Range(myoldcell).Column = 15 Then
Range(myoldcell).EntireRow.Font.Color = RGB(200, 20, 10)
End If
If Range(myoldcell) = "internal" And Range(myoldcell).Offset(0, -1) =
"internal" Then
Range(myoldcell).EntireRow.Interior.ColorIndex = 40
ElseIf Range(myoldcell) = "" Then
Range(myoldcell).EntireRow.Interior.ColorIndex = xlNone
End If
If Range(myoldcell) = "internal" And Range(myoldcell).Offset(0, 1) =
"internal" Then
Range(myoldcell).EntireRow.Interior.ColorIndex = 40
ElseIf Range(myoldcell) = "" Then
Range(myoldcell).EntireRow.Interior.ColorIndex = xlNone
End If
If Range(myoldcell) < 1 And Range(myoldcell) 0.000001 And
Range(myoldcell).Offset(-1, 0) = 1 And Range(myoldcell).Column = 20
Then
Application.EnableEvents = False
Range(myoldcell).EntireRow.Select
With Selection.Borders(xlEdgeTop)
..LineStyle = xlContinuous
..Weight = xlThick
..ColorIndex = 5
End With
Application.EnableEvents = True
Range(myoldcell).Offset(2, 0).Select
End If
If Range(myoldcell) 1 And Range(myoldcell).Offset(-1, 0) = 1 And
Range(myoldcell).Column = 20 Then
Set rng = ActiveSheet.Range(myoldcell).EntireRow
rng.Borders.LineStyle = xlNone
End If
myoldcell = ActiveCell.Address
End Sub
Cheers Christian
|