vba overlapping conditional formatting

G

Guest

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.
 
I

Ivan Raiminius

Hi Susan,

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

Regards,
Ivan
 
B

Bob Phillips

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)
 
G

Guest

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 said:
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)
Click to expand...
 
C

c.hinterbichler

Susan said:
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
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

recognizing conditional formatting with VBA 3
Excel Conditional Formatting 1
Conditional Formatting Help 3
Overwrite conditional formatting 3
Change Row Color using VBA 3
Conditional Formatting 1
Copy conditional format using formula to entire worksheet 1
conditional formatting a row 6

Top