Hello, everyone,
I've been trying to make my own macro for two months, but somehow I can't do it myself.
I am asking for your assistance on how to combine and make the conditions that I will describe.
I have a range of cells (this is in the example I'll attach) which is Range("C3:N60") - the first thing I want is to remove absolutely all Cells.FormatConditions.Delete,
after that I want in exactly selected rows to express given criteria for coloring these rows under the requested conditions (which I did manually in a macro)
I would be grateful if someone could help me because I am totally desperate with this task.
Sub test1() Range("C3:N60").Select Cells.FormatConditions.Delete End With Range("C4:N4").Select Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _ , Formula1:="=$A$4+50" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorLight2 .TintAndShade = 0.799981688894314 End With Selection.FormatConditions(1).StopIfTrue = False Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _ , Formula1:="=$A$4+100" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent3 .TintAndShade = 0.799981688894314 End With Selection.FormatConditions(1).StopIfTrue = False Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _ , Formula1:="=$A$4+150" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent6 .TintAndShade = 0.799981688894314 End With Selection.FormatConditions(1).StopIfTrue = False Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _ , Formula1:="=$A$4+200" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 255 .TintAndShade = 0 End With Selection.FormatConditions(1).StopIfTrue = False Range("C8:N8").Select Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _ , Formula1:="=$A$8+50" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorLight2 .TintAndShade = 0.799981688894314 End With Selection.FormatConditions(1).StopIfTrue = False Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _ , Formula1:="=$A$8+100" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent3 .TintAndShade = 0.799981688894314 End With Selection.FormatConditions(1).StopIfTrue = False Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _ , Formula1:="=$A$8+150" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent6 .TintAndShade = 0.799981688894314 End With Selection.FormatConditions(1).StopIfTrue = False Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _ , Formula1:="=$A$8+200" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 255 .TintAndShade = 0 End With Selection.FormatConditions(1).StopIfTrue = False Range("C3:N60").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=LEN(TRIM(C3))>0" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .Pattern = xlNone .TintAndShade = 0 End With Selection.FormatConditions(1).StopIfTrue = True Range("C2").Select End Sub




Reply With Quote
