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

 

[Peter_SSs]

I've been away for a couple of days, but for the record, here is how I would implement (with vba) my suggestion with no named ranges, and without having to estimate a suitable "last row"

Sub RedGreenLastNumber()
  Dim ws As Worksheet
 
  For Each ws In Worksheets
    If ws.Name Like Replace("...-...", ".", "[A-Z]") Then
      With ws.Range("L2", ws.Range("L" & ws.Rows.Count).End(xlUp))
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(ISNUMBER(L2),COUNT(L2:L$" & .Cells(.Cells.Count).Row & ")=1,L2<$D$11)"
        .FormatConditions(1).Interior.Color = 255
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(ISNUMBER(L2),COUNT(L2:L$" & .Cells(.Cells.Count).Row & ")=1,L2>$D$11)"
        .FormatConditions(2).Interior.Color = 5296274
      End With
    End If
  Next ws
End Sub

 

[DaleKeel]

Thank you. I deleted the LastRow As Name in the VBA and replaced it with your code saved and restarted. I also deleted reference to LastRow in the Formulas Name Management. When I copy columns from "any" worksheet I get a message that starts off with "The name 'LastRow' already exists Click yes to use that version of the name....." I am not sure what to do. Do I have to go to each sheet and delete the Conditional formating?

 

[Peter_SSs]

Thank you. I deleted the LastRow As Name in the VBA and replaced it with your code

If those comments are directed to me, then I don't think you are understanding my suggestion. My code is a complete separate code to the one suggested by John. If you want to try my code you should not be inserting it (in part part or full) into John's code.

To test my code, & test resolution of the error when you copy columns ..

1. Make a copy of the workbook & in that copy ..
2. In the Name Manager (Formulas ribbon tab) delete all the 'LastRow' names (& any other names that you are not specifically using)
3. Remove any Conditional Formatting from all sheets. On each sheet Home ribbon tab -> CF -> Clear Rules -> Clear Rules from Entire Sheet
4. In the vba editor window use the menu to Insert -> Module
5. In that new module copy/paste my code from post 11 & run that macro

 

[DaleKeel]

Thank you very very much.

 

[Peter_SSs]

You're welcome.