Conditional formating

  • Thread starter Thread starter Keith Tizzard
  • Start date Start date
K

Keith Tizzard

I have a number of cells in a spreadsheet each of which displays a date (the expiry date of some item).

I want to be able to highlight those that are within 30 days of today.

I have set a cell B17 as =Today()
and cell D17 as B17+30

Then I have added the conditional format

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=""D17"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.599963377788629
End With

This works.

However I would like not to have to have the two cells B17 and D17.

Is it possible to have a condition that is in effect:
< Today() + 30 ?


Jim
 
Hi Keith,

Am Fri, 18 Jul 2014 07:01:55 -0700 (PDT) schrieb Keith Tizzard:
I have set a cell B17 as =Today()
and cell D17 as B17+30
Is it possible to have a condition that is in effect:
< Today() + 30 ?
Click to expand...

try:

With Selection
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlLess, Formula1:="=Today()+30"
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.599963377788629
End With
End With


Regards
Claus B.
 
Hi Keith,



Am Fri, 18 Jul 2014 07:01:55 -0700 (PDT) schrieb Keith Tizzard:









try:



With Selection

.FormatConditions.Add Type:=xlCellValue, _

Operator:=xlLess, Formula1:="=Today()+30"

With .FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent4

.TintAndShade = 0.599963377788629

End With

End With





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional
Click to expand...

Thanks. That what I was trying to do but got the quote marks in the wrong place.

Simple when you know how!
 
Thanks. That what I was trying to do but got the quote marks in the wrong place.



Simple when you know how!
Click to expand...

I have implemented this and it works - up to a point.

I apply the condition to a range of cells. Those containing a date work as expected. However some cells are empty but still get their background changed as if they satisfy the condition.

How to cope with this?

Jim

PS I can handle format conditions in Access but they seem to be a bit different in Excel.
 
Hi Keith,

Am Fri, 18 Jul 2014 08:29:55 -0700 (PDT) schrieb Keith Tizzard:
I apply the condition to a range of cells. Those containing a date work as expected. However some cells are empty but still get their background changed as if they satisfy the condition.

How to cope with this?
Click to expand...

in this case use a range and a formula in CF
Try (for Range A1:M100):

Range("A1:M100").Select
With Selection
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(A1>0;A1<TODAY()+30)"
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.599963377788629
End With
End With


Regards
Claus B.
 
Back
Top