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
 
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
 
Upvote 0

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

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