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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,790
Office Version
365
Platform
Windows
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"

VBA Code:
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

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,790
Office Version
365
Platform
Windows
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
 

Forum statistics

Threads
1,078,285
Messages
5,339,296
Members
399,292
Latest member
Bdbd55

Some videos you may like

This Week's Hot Topics

Top