CONDITIONAL FORMATING LAST CELL IN COLUMN GREEN IF GREATER THAN ANOTHER CELL

DaleKeel

Board Regular
Joined
Sep 11, 2019
Messages
50
Office Version
2013
Platform
Windows
I want the last cell in column L that has a number to be green if it is a larger number than cell D11
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

DaleKeel

Board Regular
Joined
Sep 11, 2019
Messages
50
Office Version
2013
Platform
Windows
I want the last cell in column L that has a number to be green if it is a larger number than cell D11
I forgot to include if the number is smaller than the one in D11 it should be red

sorry. Thanks for your time
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,227
First, define a name (Formulas -> Define Name):

Name:LastRow
Refers to:=MATCH(9.9E+307,$L:$L)

Next, define 2 Conditional Formatting rules (both 'Use a formula to determine which cells to format'):

Formula:=AND(L1<$D$11,ROW()=LastRow)Refers to:=$L:$LFormat:Red fill
Formula:=AND(L1>$D$11,ROW()=LastRow)Refers to:=$L:$LFormat:Green fill
 

DaleKeel

Board Regular
Joined
Sep 11, 2019
Messages
50
Office Version
2013
Platform
Windows
This worked great thank you very much.
I have the same problem with 24 other sheets in the same workbook. I tried to apply the formatting rules to another sheet (without knowing I had to start over with defining a name because I have never done anything like this before) and the colored cell was two rows down from the last row with a number. But the color was correct. Then I thought I should go thru the same procedure you wrote. So I First defined the name etc. Sorry but is there a way to make a global ( I do not know what I am talking about) workbook wide something or another? If not then thanks again.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,478
Office Version
365
Platform
Windows
.. without the need to define a name.
Select L1:Lxx and apply the Conditional Formatting formula shown. Where I have $1000 it just needs to be a row below where your data will ever get to.

Book1
DL
1Numbers
245
334
423
5
6
7
84
933
1044
1148
Green Red
Cells with Conditional Formatting
CellConditionCell FormatStop If True
L1:L20Expression=AND(ISNUMBER(L1),COUNT(L1:L$10000)=1,L1<$D$11)textNO
L1:L20Expression=AND(ISNUMBER(L1),COUNT(L1:L$10000)=1,L1>$D$11)textNO
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,227
This worked great thank you very much.
I have the same problem with 24 other sheets in the same workbook. I tried to apply the formatting rules to another sheet (without knowing I had to start over with defining a name because I have never done anything like this before) and the colored cell was two rows down from the last row with a number. But the color was correct. Then I thought I should go thru the same procedure you wrote. So I First defined the name etc. Sorry but is there a way to make a global ( I do not know what I am talking about) workbook wide something or another? If not then thanks again.
You have to define the LastRow name and the Conditional Formatting rules on each sheet, which this macro does:
VBA Code:
Public Sub Create_CF_Rules()

    Dim ws As Worksheet
    Dim LastRow As Name
   
    For Each ws In ActiveWorkbook.Worksheets
   
        'Add or update LastRow name on this sheet
       
        On Error Resume Next
        Set LastRow = ws.Names("LastRow")
        On Error GoTo 0
        If LastRow Is Nothing Then
            ws.Names.Add Name:="LastRow", RefersTo:="=MATCH(9.9E+307,$L:$L)"
        Else
            LastRow.RefersTo = "=MATCH(9.9E+307,$L:$L)"
        End If
       
        'Add or update Conditional Formatting rules in column L on this sheet
       
        With ws.Columns("L:L")
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(L1<$D$11,ROW()=LastRow)"
            With .FormatConditions(.FormatConditions.Count)
                .SetFirstPriority
                .StopIfTrue = False
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .Color = 255
                    .TintAndShade = 0
                End With
            End With
            .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(L1>$D$11,ROW()=LastRow)"
            With .FormatConditions(.FormatConditions.Count)
                .SetFirstPriority
                .StopIfTrue = False
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .Color = 5287936
                    .TintAndShade = 0
                End With
            End With
   
        End With
       
    Next
   
End Sub
If you want to use Peter's method then the code is very similar: just delete the LastRow lines, change ws.Columns("L:L") to ws.Range("L1:L10000") and change the Formula1 strings to Peter's formulae.
 

DaleKeel

Board Regular
Joined
Sep 11, 2019
Messages
50
Office Version
2013
Platform
Windows
John, Thank you very much for helping me. I really feel like an idiot. I am VERY new at ANY advanced work. So I am not precise in my definition of a problem. Besides the 24 other sheets with this requirement there are several to many sheets with other data on them that the VBA code should not be applied to. The only sheets(tabs) that has this requirement have the same naming configuration. xxx-yyy examples are like ABC-CDF , GHI-JKL and MNO-QRS Is there a way to list these in the VBA so that the VBA code only effects these sheets(tabs)

Again I apologize for wasting your time.

Thank You
 

DaleKeel

Board Regular
Joined
Sep 11, 2019
Messages
50
Office Version
2013
Platform
Windows
Never Mind. I put your code in one of the sheets and ran it. The code worked on every page it was intended to work on. It also worked on other sheets that had numbers in column L and D. On the ones that I did not want it to work on I went to the conditional formatting button and deleted them. Saved the file and looked at them again and the deletion worked. So I have the code working on the pages that I wanted on and not on the others.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,227
To process only the sheets named with the format XXX-YYY, we add an If statement inside the loop:
VBA Code:
Public Sub Create_CF_Rules()

    Dim ws As Worksheet
    Dim LastRow As Name
    
    For Each ws In ActiveWorkbook.Worksheets
    
        If ws.Name Like "[A-Z][A-Z][A-Z]-[A-Z][A-Z][A-Z]" Then
        
            'Add or update LastRow name on this sheet
            
            On Error Resume Next
            Set LastRow = ws.Names("LastRow")
            On Error GoTo 0
            If LastRow Is Nothing Then
                ws.Names.Add Name:="LastRow", RefersTo:="=MATCH(9.9E+307,$L:$L)"
            Else
                LastRow.RefersTo = "=MATCH(9.9E+307,$L:$L)"
            End If
            
            'Add or update Conditional Formatting rules in column L on this sheet
            
            With ws.Columns("L:L")
                .FormatConditions.Delete
                .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(L1<$D$11,ROW()=LastRow)"
                With .FormatConditions(.FormatConditions.Count)
                    .SetFirstPriority
                    .StopIfTrue = False
                    With .Interior
                        .PatternColorIndex = xlAutomatic
                        .Color = 255
                        .TintAndShade = 0
                    End With
                End With
                .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(L1>$D$11,ROW()=LastRow)"
                With .FormatConditions(.FormatConditions.Count)
                    .SetFirstPriority
                    .StopIfTrue = False
                    With .Interior
                        .PatternColorIndex = xlAutomatic
                        .Color = 5287936
                        .TintAndShade = 0
                    End With
                End With
        
            End With
        
        End If
        
    Next
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,096,432
Messages
5,450,394
Members
405,608
Latest member
griffindor2020

This Week's Hot Topics

Top