How to apply mass conditional formatting with a formula

[Revvy]

Hi all

Im trying to make a conditional format that will turn a cell either green or red, depending on if it is equal to or greater than the cell next to it.

In this case its a date, if a task has been completed in time i want it green, and if it was completed late to turn red.

Ive achieved this in one cell, but cannot make it apply to the entire row to always use the cell next to it.

All help appreciated, im very self taught and have big gaps in my excel knowledge!

 

[James006]

Hi,
Which are the Columns you need to format ?
What is the rule you want to apply ?

 

[Wookiee]

I created this macro after having recorded myself applying the 2 different conditional formats. Since I don't know the specific column(s) you want to utilize, I dummied up some data in Columns G and H and crafted this macro based on adding conditional formatting to cells H4:H9; you'll need to alter the formulas to reflect the proper column(s).

Sub ConditionTheFOuttaThemCells()

With Selection

  .FormatConditions.Add _
    Type:=xlExpression, _
    Formula1:="=$H4>$G4"
  
  .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  
  With .FormatConditions(1).Interior
  
   .Color = vbRed
  
  End With
  
  .FormatConditions(1).StopIfTrue = False

  .FormatConditions.Add _
    Type:=xlExpression, _
    Formula1:="=$H4<$G4"
  
  .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  
  With .FormatConditions(1).Interior
  
   .Color = vbGreen
  
  End With
  
  .FormatConditions(1).StopIfTrue = False


End With


End Sub

 

[Peter_SSs]

Im trying to make a conditional format

We cannot actually see your column or row labels so you may have to adjust, but for my layout below, I selected all the way from C2 (that is, the top-left 'Date Completed' cell) across and down to G10 (what I decided might be my bottom-right 'Date Completed' cell) and applied the Conditional Formatting shown.

23 03 11.xlsm
	B	C	D	E	F	G
1	Week ..	DATE COMPLETED	Week ..	DATE COMPLETED	Week ..	DATE COMPLETED
2	8/03/2023	9/03/2023	29/03/2023		5/04/2023
3	8/01/2022		29/01/2022	25/12/2021	5/02/2022
4	8/11/2022		29/11/2022	29/11/2022	6/12/2022
5	8/12/2022		29/11/2022		5/01/2023	7/03/2023
6
7
8
9
10
Red Green

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:G10	Expression	=AND(LEFT(C$1,4)="DATE",C2<>"",C2<=B2)	text	NO
C2:G10	Expression	=AND(LEFT(C$1,4)="DATE",C2<>"",C2>B2)	text	NO

 

[Revvy]

Will give these a go today!