Find cell containing current date and format column

0
votes

I am working with an order guide for my job using excel. I've been manually formatting columns to shade any cell greater than 0 so I can more easily see what I have to put into my order program.

Each date spans 3 columns and every row below the date has 3 individual cells for each date: sold, inventory, and order. I only want to format orders for the day I'm writing the order on. For example, if column D has today's date, I want to format every cell in column F that is greater than 0.

I know the logic behind it. Check row 1 for today's date (I'm assuming using TODAY()), then offset 2 columns to the right, then format each cell greater than 0. I just don't know how to write this into a formula.

Any help would be greatly appreciated. I'm new to excel, so I'm not really sure as to how writing formulas works.

2
excelformula

2 Answers

1
votes

Assuming I am guessing correctly and that your first row with a date is Row2, then select F3 and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=AND(F3>0,D2=TODAY())

Format..., select highlighting of choice, OK, OK, change Applies to to:

=$F$3:$F$#

where # represents a suitable row number, Apply.

0
votes

Create a conditional formatting rule based on the following formula,

=and($d1=today(), f1>0)

This VBA will create a CFR for columns F:Z.

With worksheets("sheet1").range("F:Z")
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=and($d1=today(), f1>0)"
    .FormatConditions(.FormatConditions.Count).font.Color = vbred
End With