Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional format column
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional format column
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!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional format column
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-B330; D4-B330;D5-B330 "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!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional format column
Based on your new information, this should do it:Sub Macro1()
n = Cells(Rows.Count, "E").End(xlUp).Row Range("D1:D" & n).Select Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="=$B$3" Selection.FormatConditions(Selection.FormatConditi ons.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-B330; D4-B330;D5-B330 "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!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional format column
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-B330" 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:D" & n).Select Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="=$B$3" Selection.FormatConditions(Selection.FormatConditi ons.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-B330; D4-B330;D5-B330 "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!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional format column
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:D" & 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:D" & n).Select Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="=$B$3" Selection.FormatConditions(Selection.FormatConditi ons.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-B330; D4-B330;D5-B330 "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!! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional format column
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:D" & 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:D" & n).Select Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="=$B$3" Selection.FormatConditions(Selection.FormatConditi ons.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-B330; D4-B330;D5-B330 "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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional format a column based on value in a different column | Excel Worksheet Functions | |||
Conditional format row by column date | Excel Discussion (Misc queries) | |||
Conditional format entire column | Excel Worksheet Functions | |||
Conditional Format a column based on another | Excel Discussion (Misc queries) | |||
Conditional format a column | Excel Discussion (Misc queries) |