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!!

Take a look at this:
http://www.datapigtechnologies.com/f...rmatexcel.html

This is good too:
http://www.datapigtechnologies.com/f...tlformats.html

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Peruanos72" wrote:

> 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!!

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" wrote:

> Take a look at this:
> http://www.datapigtechnologies.com/f...rmatexcel.html
>
> This is good too:
> http://www.datapigtechnologies.com/f...tlformats.html
>
> HTH,
> Ryan---
>
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
>
>
> "Peruanos72" wrote:
>
> > 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!!

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

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Peruanos72" wrote:

> 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" wrote:
>
> > Take a look at this:
> > http://www.datapigtechnologies.com/f...rmatexcel.html
> >
> > This is good too:
> > http://www.datapigtechnologies.com/f...tlformats.html
> >
> > HTH,
> > Ryan---
> >
> > --
> > Ryan---
> > If this information was helpful, please indicate this by clicking ''Yes''.
> >
> >
> > "Peruanos72" wrote:
> >
> > > 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!!

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.

"ryguy7272" wrote:

> 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---
>
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
>
>
> "Peruanos72" wrote:
>
> > 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" wrote:
> >
> > > Take a look at this:
> > > http://www.datapigtechnologies.com/f...rmatexcel.html
> > >
> > > This is good too:
> > > http://www.datapigtechnologies.com/f...tlformats.html
> > >
> > > HTH,
> > > Ryan---
> > >
> > > --
> > > Ryan---
> > > If this information was helpful, please indicate this by clicking ''Yes''.
> > >
> > >
> > > "Peruanos72" wrote:
> > >
> > > > 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!!

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" wrote:

> 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---
>
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
>
>
> "Peruanos72" wrote:
>
> > 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" wrote:
> >
> > > Take a look at this:
> > > http://www.datapigtechnologies.com/f...rmatexcel.html
> > >
> > > This is good too:
> > > http://www.datapigtechnologies.com/f...tlformats.html
> > >
> > > HTH,
> > > Ryan---
> > >
> > > --
> > > Ryan---
> > > If this information was helpful, please indicate this by clicking ''Yes''.
> > >
> > >
> > > "Peruanos72" wrote:
> > >
> > > > 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!!

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


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Peruanos72" wrote:

> 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" wrote:
>
> > 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---
> >
> > --
> > Ryan---
> > If this information was helpful, please indicate this by clicking ''Yes''.
> >
> >
> > "Peruanos72" wrote:
> >
> > > 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" wrote:
> > >
> > > > Take a look at this:
> > > > http://www.datapigtechnologies.com/f...rmatexcel.html
> > > >
> > > > This is good too:
> > > > http://www.datapigtechnologies.com/f...tlformats.html
> > > >
> > > > HTH,
> > > > Ryan---
> > > >
> > > > --
> > > > Ryan---
> > > > If this information was helpful, please indicate this by clicking ''Yes''.
> > > >
> > > >
> > > > "Peruanos72" wrote:
> > > >
> > > > > 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!!