Conditional formating

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
 
C

Claus Busch

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 ?

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.
 
K

Keith Tizzard

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

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

Simple when you know how!
 
K

Keith Tizzard

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



Simple when you know how!

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.
 
C

Claus Busch

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?

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top