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

[DaleKeel]

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]

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]

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:$L	Format:	Red fill
Formula:	=AND(L1>$D$11,ROW()=LastRow)	Refers to:	=$L:$L	Format:	Green fill

 

[DaleKeel]

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]

.. 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
	D								L
1									Numbers
2									45
3									34
4									23
5
6
7
8									4
9									33
10									44
11	48
Green Red

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L1:L20	Expression	=AND(ISNUMBER(L1),COUNT(L1:L$10000)=1,L1<$D$11)	text	NO
L1:L20	Expression	=AND(ISNUMBER(L1),COUNT(L1:L$10000)=1,L1>$D$11)	text	NO

 

[John_w]

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:

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]

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]

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]

To process only the sheets named with the format XXX-YYY, we add an If statement inside the loop:

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

 

[DaleKeel]

Thank you again and again.