Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formatting using VB and range
I'm trying to update an existing macro to add conditional formatting to a
range of cells. The current macro defines the range of cells to be updated each time it runs as the range of cells is not always the same. The range of cells is then named AttRange1. I used the macro recorder to capture the basic formatting syntax but need help adding it to the original macro. If the cell begins with the letter "E" I need to change the font to red bold and this is what it gave me: Range("J14").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=LEFT(J14)=""E""" With Selection.FormatConditions(1).Font .Bold = True .Italic = False .ColorIndex = 3 End With ....two similar formatting conditions applied here... Selection.Copy Range("J14:Q35").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False In this case, the range begins in J14 and goes to Q35. I set the formatting in J14 and then used the format painter to apply it to the rest of the range. If my starting cell was always J14 I think I could get away with changing the line after the copy command from "Range("J14:Q35").Select" to "AttRange1.Select" but it is always different. Is there anyway I can update the code in line 1 and 3 above to use my range of cells AttRange1? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formatting using VB and range
This should work if the range has been named at the
point that this snippet is used. Dim c As Range For Each c In Range("AttRange1") If c.FormatConditions.Count < 1 Then With c.FormatConditions .Add Type:=xlExpression, _ Formula1:="=Left(" & c.Address & ", 1)=""E""" With c.FormatConditions(1).Font .Bold = True .ColorIndex = 3 End With End With End If Next "PJ" wrote: I'm trying to update an existing macro to add conditional formatting to a range of cells. The current macro defines the range of cells to be updated each time it runs as the range of cells is not always the same. The range of cells is then named AttRange1. I used the macro recorder to capture the basic formatting syntax but need help adding it to the original macro. If the cell begins with the letter "E" I need to change the font to red bold and this is what it gave me: Range("J14").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=LEFT(J14)=""E""" With Selection.FormatConditions(1).Font .Bold = True .Italic = False .ColorIndex = 3 End With ...two similar formatting conditions applied here... Selection.Copy Range("J14:Q35").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False In this case, the range begins in J14 and goes to Q35. I set the formatting in J14 and then used the format painter to apply it to the rest of the range. If my starting cell was always J14 I think I could get away with changing the line after the copy command from "Range("J14:Q35").Select" to "AttRange1.Select" but it is always different. Is there anyway I can update the code in line 1 and 3 above to use my range of cells AttRange1? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formatting using VB and range
Thanks JLG. I'm out of the office today so I will test it out next week.
One follow up question though. I have two other conditions I want to add. So after the last line in your code ("Next"), can I simply re-paste the same code again for each of the two other conditions? I assume I can just update the code and use "d" and "e" for the next two conditions along with updating the formula line. Thanks again. "JLGWhiz" wrote: This should work if the range has been named at the point that this snippet is used. Dim c As Range For Each c In Range("AttRange1") If c.FormatConditions.Count < 1 Then With c.FormatConditions .Add Type:=xlExpression, _ Formula1:="=Left(" & c.Address & ", 1)=""E""" With c.FormatConditions(1).Font .Bold = True .ColorIndex = 3 End With End With End If Next "PJ" wrote: I'm trying to update an existing macro to add conditional formatting to a range of cells. The current macro defines the range of cells to be updated each time it runs as the range of cells is not always the same. The range of cells is then named AttRange1. I used the macro recorder to capture the basic formatting syntax but need help adding it to the original macro. If the cell begins with the letter "E" I need to change the font to red bold and this is what it gave me: Range("J14").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=LEFT(J14)=""E""" With Selection.FormatConditions(1).Font .Bold = True .Italic = False .ColorIndex = 3 End With ...two similar formatting conditions applied here... Selection.Copy Range("J14:Q35").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False In this case, the range begins in J14 and goes to Q35. I set the formatting in J14 and then used the format painter to apply it to the rest of the range. If my starting cell was always J14 I think I could get away with changing the line after the copy command from "Range("J14:Q35").Select" to "AttRange1.Select" but it is always different. Is there anyway I can update the code in line 1 and 3 above to use my range of cells AttRange1? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting using a range | Excel Discussion (Misc queries) | |||
Conditional Formatting Range | Excel Discussion (Misc queries) | |||
Conditional Formatting - Range | Excel Programming | |||
Conditional Formatting Range | Excel Programming | |||
Conditional Formatting In a Range | Excel Discussion (Misc queries) |