Conditional Formatting Help Needed!

[dukefan]

I am trying to set up 2 conditional formats. When row E is highlighted, I would like a conditional format where directly under variance if it shows a value greater than $0.00 it highlight background as orange similar how it shows highlighted when you see PENDING OR APPROVED. So anytime a value is greater than 0 in that row directly under variance it highlight background as orange.

Next, when row F is highlighted, I would like a conditional format where directly under OUSTANDING VAR, if value is $0.00, it should highlight background as red and if greater than $0.00 it should hightlight in green.

Under DECISION, I have that set to a Data Validation using list condition.

I want the the conditional formats based on the whole column and not by each cycle as you see there are 3 cycles now.

Please share screenshots of the conditional formats you recommend and what the results will show overall when it runs.

By doing each cycle where it has separate conditional formats which is how it it set up now as I copy and paste a blank cycle undereath each one that is complete to repeat the cycle, it slows the spreadsheet down and takes a bit to up open to as it has to recalculate through every conditional format in the conditional format section.

Please note, I will have the same information across as many has 45 tabs so I will have the same conditional formatting across all 45 tabs. If you have any way to set that up, please advise of your recommendation.

If you have questions, please reply with any and I will do my best to answer them.

Thank you in advance for any help you may provide in solving this issue.

 

[dukefan]

Changed to editor

 

[dukefan]

Vincent,

Check your email.

Chris

 

[coulombevin]

Vincent,

Here is what I did and it is not working for me.

View attachment 121168

Hi Chris,

I changed your worksheet. The problem was the header, "OUST" instead of "OUTST".

Bests regards,

Vincent

 

[coulombevin]

Hi Chris,

Here's my VBA option attempt : *I am using the French version of Excel 365, so there might be some errors in my VBA due to manual translation or discrepancies in the Excel user manual*

Sub applyConditionsToAllSheets()
    Dim ws As Worksheet
    'loop all your ws
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            With .Range("F2:F1048576")
                'conditional for pending
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""PENDING"""
                'set first (1)
                .FormatConditions(.FormatConditions.Count).SetFirstPriority
                'yellowish
                With .FormatConditions(1)
                    With .Font
                        .Color = -16754788
                        .TintAndShade = 0
                    End With
                    With .Interior
                        .PatternColorIndex = xlAutomatic
                        .Color = 10284031
                        .TintAndShade = 0
                    End With
                    .StopIfTrue = False
                End With
                
                'conditional for approved
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""APPROVED"""
                'set first (1)
                .FormatConditions(.FormatConditions.Count).SetFirstPriority
                'greenish
                With .FormatConditions(1)
                    With .Font
                        .Color = -16752384
                        .TintAndShade = 0
                    End With
                    With .Interior
                        .PatternColorIndex = xlAutomatic
                        .Color = 13561798
                        .TintAndShade = 0
                    End With
                    .StopIfTrue = False
                End With
                
                'conditional for outstanding greather
                .FormatConditions.Add Type:=xlExpression, Formula1:="=AND($F1=""OUTSTANDING VAR"",$F2>0)"
                'set first (1)
                .FormatConditions(.FormatConditions.Count).SetFirstPriority
                'redish
                With .FormatConditions(1)
                    With .Font
                        .Color = -16383844
                        .TintAndShade = 0
                    End With
                    With .Interior
                        .PatternColorIndex = xlAutomatic
                        .Color = 13551615
                        .TintAndShade = 0
                    End With
                    .StopIfTrue = False
                End With
                
                'conditional for outstanding equal
                .FormatConditions.Add Type:=xlExpression, Formula1:="=AND($F1=""OUTSTANDING VAR"",$F2=0)"
                'set first (1)
                .FormatConditions(.FormatConditions.Count).SetFirstPriority
                'redish
                With .FormatConditions(1)
                    With .Font
                        .Color = -16752384
                        .TintAndShade = 0
                    End With
                    With .Interior
                        .PatternColorIndex = xlAutomatic
                        .Color = 13561798
                        .TintAndShade = 0
                    End With
                    .StopIfTrue = False
                End With
            End With
            
            With .Range("E2:E1048576")
                'conditional for variance
                .FormatConditions.Add Type:=xlExpression, Formula1:="=AND($E1=""VARIANCE"",$E2>0)"
                'set first (1)
                .FormatConditions(.FormatConditions.Count).SetFirstPriority
                'orangeish
                With .FormatConditions(1)
                    With .Interior
                        .PatternColorIndex = xlAutomatic
                        .Color = 957426
                        .TintAndShade = 0
                    End With
                    .StopIfTrue = False
                End With
            End With
        End With
    Next
End Sub

bests regards,

Vincent

 

[dukefan]

Thank you Vincent for your help

 

[coulombevin]

Hi Chris,

No problem! Could you confirm if it work and mark as Solution/solved?

See you around,

Vincent