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

DaleKeel

Board Regular
Joined
Sep 11, 2019
Messages
56
Office Version
  1. 2013
Platform
  1. 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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
.. 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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,928
Messages
6,110,737
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top