vba overlapping conditional formatting

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

Hi Susan,

use macro recorder while applying conditional formatting to see the
code.

Regards,
Ivan

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >