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

DaleKeel

New Member
Joined
Sep 11, 2019
Messages
27
I want the last cell in column L that has a number to be green if it is a larger number than cell D11
 

DaleKeel

New Member
Joined
Sep 11, 2019
Messages
27
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
5,957
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

New Member
Joined
Sep 11, 2019
Messages
27
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
41,800
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.

xl2bb.xlam
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
5,957
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

New Member
Joined
Sep 11, 2019
Messages
27
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

New Member
Joined
Sep 11, 2019
Messages
27
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
5,957
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
 

Forum statistics

Threads
1,078,441
Messages
5,340,299
Members
399,364
Latest member
farhan11941234

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top