Consulting

Results 1 to 20 of 33

Thread: Delete all conditional formatting and coloring conditional on specific rows

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Tutor
    Joined
    Sep 2012
    Location
    London
    Posts
    264
    Location

    Delete all conditional formatting and coloring conditional on specific rows

    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
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •