Solved: Conditional Formating with multiple If statements and conditions

[chris09q5]

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!


[vba]
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

[/vba]

[Simon Lloyd]

You cannot reference a whole column by Columns("M" & x).Select, that would equate to something like Columns("M1").Select which of course will give an error as it doesn't exist, do you mean for each cell in that column?, if so then a case statement like this may help:
[vba]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
Dim Cel As Range
For x = 2 To LastRow
Select Case Range("N" & x).Value
Case Is = "WAREHOUSES"
Range("M" & x).Interior.ColorIndex = 3
Case Is = "Something Else"
Range("M" & x).Interior.ColorIndex = 4
Case Is = "Something Different"
Range("M" & x).Interior.ColorIndex = 5
Case Is = ""
Range("M" & x).Interior.ColorIndex = xlNone
''''''The above case is statement can just be extended for as many as you need
End Select
Next x[/vba]

[chris09q5]

Thanks that worked!