Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba overlapping conditional formatting
Hi Susan,
use macro recorder while applying conditional formatting to see the code. Regards, Ivan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba overlapping conditional formatting
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba overlapping conditional formatting
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot tables and conditional formatting and overlapping | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
Overlapping conditional formats | Excel Programming |