chris09q5
09-04-2009, 05:40 AM
Hi Everyone,
I am currently trying to write an if statement that checks a column for certain text then applies conditional formatting to a different column based on the value of that cell. The code I am currently using is applying the conditional formatting to the entire column instead of just the rows that have the text specified. Can you help? Thanks!
Dim LastRow As Integer
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Dim Limit As Long
Limit = ActiveSheet.UsedRange.Rows.Count
Range("M2:M" & Limit).Select
For Each Cell In Selection
Cell.Value = Val(Cell.Value)
Next
Selection.NumberFormat = "General"
Worksheets("Details").Range("A1:R" & LastRow).Sort Key1:=Worksheets("Details").Columns("N"), Order1:=xlAscending, _
Header:=xlYes, Key2:=Worksheets("Details").Columns("M"), Order1:=xlDescending, Header:=xlYes
For x = 2 To LastRow
If Range("N" & x) = "WAREHOUSES" Then
Columns("M" & x).Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=85"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 12611584
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=85", Formula2:="=50"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=50", Formula2:="=10"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=10"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End If
Next x
I am currently trying to write an if statement that checks a column for certain text then applies conditional formatting to a different column based on the value of that cell. The code I am currently using is applying the conditional formatting to the entire column instead of just the rows that have the text specified. Can you help? Thanks!
Dim LastRow As Integer
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Dim Limit As Long
Limit = ActiveSheet.UsedRange.Rows.Count
Range("M2:M" & Limit).Select
For Each Cell In Selection
Cell.Value = Val(Cell.Value)
Next
Selection.NumberFormat = "General"
Worksheets("Details").Range("A1:R" & LastRow).Sort Key1:=Worksheets("Details").Columns("N"), Order1:=xlAscending, _
Header:=xlYes, Key2:=Worksheets("Details").Columns("M"), Order1:=xlDescending, Header:=xlYes
For x = 2 To LastRow
If Range("N" & x) = "WAREHOUSES" Then
Columns("M" & x).Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=85"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 12611584
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=85", Formula2:="=50"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=50", Formula2:="=10"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=10"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End If
Next x