DanG
Registered User.
- Local time
- Today, 07:36
- Joined
- Nov 4, 2004
- Messages
- 477
Hi,
The crappy code I made below is designed to condisionaly format on 2 columns, "Group Nmber" and "50/50 Category". Under "Group Number" I want all rows highlighted grey that are "or" contain "69472". And "50/50" I would like the entire row to be bold and italic if the cell contains either "Default" or "At Risk".
The code below works, but does not highlight the row grey if the "50/50" column contains a "default" or "at risk", it will only bold and italicise that row.
Help
The crappy code I made below is designed to condisionaly format on 2 columns, "Group Nmber" and "50/50 Category". Under "Group Number" I want all rows highlighted grey that are "or" contain "69472". And "50/50" I would like the entire row to be bold and italic if the cell contains either "Default" or "At Risk".
The code below works, but does not highlight the row grey if the "50/50" column contains a "default" or "at risk", it will only bold and italicise that row.
Help
Code:
' Make 50/50 bold and highlight GA# 69472 -----------------
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim MyCol As String
Dim MySDA As String
Rows("1:1").Select
Selection.Find(What:="*50/50*", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
MyCol = "$" & Split(ActiveCell.Address, "$")(1) & "1"
Rows("1:1").Select
Selection.Find(What:="Group Numbers", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
MySDA = "$" & Split(ActiveCell.Address, "$")(1) & "1"
Range("A1:CA6000").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF(OR(" & MyCol & "=""At Risk""," & MyCol & "=""Default: Please Reassign""),1,0)"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
End With
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIF(" & MySDA & "," & Chr(34) & Chr(42) & "69472" & Chr(42) & Chr(34) & ")>0"
Selection.FormatConditions(2).Interior.ColorIndex = 15
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIF(" & MySDA & "," & "69472" & ")>0"
Selection.FormatConditions(3).Interior.ColorIndex = 15
' End Make 50/50 bold ----------------------------------------------