conditional format column

[Peruanos72]

Hello,

In column "D" I have a date in each cell and I want to add a conditional
format
for each cell where if that cell - B3 > 30 the cell bolds and the font turns
red.
Cell B3 is a date as well. I'm using the last cell in column "E" to find the
range.

Thanks!!

 

[ryguy7272]

Take a look at this:
http://www.datapigtechnologies.com/flashfiles/conditionalformatexcel.html

This is good too:
http://www.datapigtechnologies.com/flashfiles/fourcondtlformats.html

HTH,
Ryan---

 

[Peruanos72]

That's good info but what i'm looking for is code that will create a
conditional
format for each cell in column D where for each individual cell minus(-)
cell B3 > 30
the cell is formated. What I'm having trouble with is writing the code to
apply the format to each individual cell. Cell B3 remains constant but the
references for the other cells will change.

Ex: D3-B3>30; D4-B3>30;D5-B3>30

 

[ryguy7272]

Based on your new information, this should do it:Sub Macro1()
n = Cells(Rows.Count, "E").End(xlUp).Row
Range("D1" & n).Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=$B$3"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

NOTICE: This is for Excel 2007. Not sure what version you use. I'm at
school now; all we have here is 2007. I can test on 2003 when I get home
tonight, won't be until after 11 though.

HTH,
Ryan---

 

[Peruanos72]

no worries. i have 2003

i did receive an application/object defined error on line 3

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=$B$3"

the formula is ("each cell in col D" based on the range used) minus (-) B3

30 then format cell. I tried the following

formula but it didn't work. Ex" Formula1:="=RC-B3>30"

Extra info...the data in each cell in column D is a date and i'm subtracting
that date from the date in cell B3. if the difference is greater than 30 the
cell formats.

 

[Peruanos72]

Hey Ryan,

I was able to make it work. I changed the formula I was using so I didn't
have to reference each cell in column D. This is what I have. Thanks again
for your help.

lastrow = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row
Range("D7" & lastrow).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=$B$3-30"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With

 

[ryguy7272]

Awesome!! You are on your way to becoming a VBA expert!!
Ryan--