Prev: Excel 2003 - Button moves along with the cell selection
Next: Function Last Row()
From: peedub on 26 Oct 2009 18:06
I have a pivot table that is linked to data in a project/resource
management system. I want to apply conditional formatting so that I
can (1) highlight project entries based on their current state, (2)
format the total rows for each resource so I can highlight those that
are over committed (total hours > 40) and those that are under-
utilized (total hours < 35). So, I'm good through creating the
conditional formatting rules for the first item. I'm getting hung
creating conditional formatting for the second scenario. My thinking
is that since each resource's total row is a separate range, I have to
create a separate rule for each. What's odd is that Excel creates the
rule correctly, but then applies the formatting instructions to a
completely different rule. No matter what (i) value I use, the
formatting instructions always get applied to one of the first two
rules I created. What am I doing wrong? Here's my code:

Sub FormatBookedHours()

Dim sBegRange As String
Dim sCFCell As String
Dim sEndRange As String
Dim sStageStart As String
Dim rTable As Range
Dim sTargetCell As String
Dim i As Integer
Dim iA As Integer
Dim iB As Integer

'Make sure the focus is the Report tab
Sheets("Report").Select
Range("A1").Select

'Step 1 - Format the opportunity and delivering rows
'First find the Project Stage column
Cells.Find(What:="Project Stage", After:=Range("A1"),
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
ActiveCell.Select
sStageStart = ActiveCell.Address
ActiveCell.Offset(1, -1).Select
sBegRange = ActiveCell.Address

'Find the Grand Total column
Cells.Find(What:="Grand Total", After:=Range("B1"),
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
ActiveCell.Select
ActiveCell.Offset(2, 0).Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt
_
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
ActiveCell.Select
ActiveCell.Offset(-2, 0).Select
sEndRange = ActiveCell.Address

'Create the conditional formatting rule for Opportunity
Range(sStageStart).Select
ActiveCell.Offset(1, 0).Select
sStageStart = ActiveCell.Address
sStageStart = Replace(sStageStart, "$", "")
sStageStart = "$" & sStageStart
With Range(sBegRange, sEndRange)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & sStageStart & "=""Opportunity"""
.FormatConditions(1).Interior.ColorIndex = 40
.FormatConditions(1).StopIfTrue = False
End With
'Create the conditional formatting rule for Delivery
With Range(sBegRange, sEndRange)
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & sStageStart & "=""Delivery"""
.FormatConditions(2).Interior.ColorIndex = 43
.FormatConditions(2).StopIfTrue = False
End With

'Step 2 - Hide the Project Stage column
Range(sBegRange).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.EntireColumn.Hidden = True

'Step 3 - Format Total hours rows - bold, set font color red for >
40, background yellow for < 35
Range("A1").Select
Cells.Find(What:="Resource", After:=Range("A1"),
LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
ActiveCell.Select

Cells.Find(What:=") Total", After:=Range("A1"),
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
ActiveCell.Select
sBegRange = ActiveCell.Address
iA = ActiveCell.Row
ActiveCell.Offset(2000, 0).Select
sEndRange = ActiveCell.Address
Range(sEndRange).End(xlUp).Select

'The first two conditional format rules are in place
'the next rule should be number 3, so set the
'index value for the next rule to 3 and then
'increment for each rule that is created
i = 3

Do
'Start at the beginning of the row
Range(sBegRange).Select
ActiveCell.Offset(0, 30).Select
sEndRange = ActiveCell.Address
'Find the last cell in the row
Range(sEndRange).End(xlToLeft).Select
ActiveCell.Offset(0, -1).Select
sEndRange = ActiveCell.Address
'Format the row
Range(sBegRange, sEndRange).Select
Selection.Font.FontStyle = "Bold"
'Set the sBegRange value to the first cell with a number
Range(sBegRange).Select
ActiveCell.Offset(0, 4).Activate
sBegRange = ActiveCell.Address
sCFCell = Replace(sBegRange, "$", "")
'Create the conditional format to make all hours over 40 red
With Range(sBegRange, sEndRange)
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & sCFCell & ">40"
.FormatConditions(i).StopIfTrue = False
.FormatConditions(i).Font.ColorIndex = 3
End With
'Create the conditional format to make all hours under 35 blue
i = i + 1
With Range(sBegRange, sEndRange)
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & sCFCell & "<35"
.FormatConditions(i).StopIfTrue = False
.FormatConditions(i).Font.ColorIndex = 5
End With
'Find the next starting point
Range(sBegRange).Select
ActiveCell.Offset(0, -4).Activate
sBegRange = ActiveCell.Address
Cells.Find(What:=") Total", After:=Range(sBegRange),
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, MatchCase:= _
False).Activate
sBegRange = ActiveCell.Address
iB = iA
iA = ActiveCell.Row
i = i + 1
Loop Until iA < iB

Range("A1").Select

End Sub
From: peedub on 28 Oct 2009 12:40
Another interesting note about this:

At the point that I create the new rule and then edit it to set the
StopIfTrue value, that edit is applied to the correct rule. The very
next step, setting the font color, is applied to a different rule.
This is the section I'm talking about:

..FormatConditions(i).StopIfTrue = False 'Gets applied to the correct
rule
..FormatConditions(i).Font.ColorIndex = 3 'Gets applied to a different
rule
 | 
Pages: 1
Prev: Excel 2003 - Button moves along with the cell selection
Next: Function Last Row()