| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Borders based on ByVal Target As Range
Greetings:
I have a Yes/No pulldown menu in cell F229. My spreadsheet has a white background (color index 0). If "Yes" is selected, I want some text to appear in Cell H229 (the text is currently there, I just want to change it to font color index 0 so that it will be visible), and I want to turn the background of cell J229 yellow and put a border around it. If F229 is selected as "No" or is blank, I want the text in H229 to go invisible (not delete) and reset the yellow bordered cell back to white background with no borders. I recorded and modified a macro. Here is the relevant code (declarations not included): If Target.Cells(1).Address = "$F$229" Then 'Make text in H229 bordered box with yellow background appear if "Yes" in F229 Application.EnableEvents = False If MM.Range("F229") = "Yes" Then Range("H229").Font.ColorIndex = 0 'OK Range("J229").Interior.ColorIndex = 6 'OK Range("J229").Borders(xlEdgeLeft).ColorIndex = xlAutomatic 'OK Range("J229").Borders(xlEdgeTop).ColorIndex = xlAutomatic 'Not executing Range("J229").Borders(xlEdgeBottom).ColorIndex = xlAutomatic 'Not executing Range("J229").Borders(xlEdgeRight).ColorIndex = xlAutomatic 'Not executing Else Range("H229").Font.ColorIndex = 2 'OK Range("J229").Interior.ColorIndex = 0 'OK Range("J229").Borders(xlEdgeLeft).LineStyle = xlNone 'OK Range("J229").Borders(xlEdgeTop).LineStyle = xlNone 'OK Range("J229").Borders(xlEdgeBottom).LineStyle = xlNone 'OK Range("J229").Borders(xlEdgeRight).LineStyle = xlNone 'OK End If Application.EnableEvents = True End If The problem: When I select "Yes" in F229, I only get the LEFT border on the yellow cell. The other three borders don't appear. Any thoughts as to what's going on? Help is appreciated. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Add Borders based on ByVal Target As Range
Marty,
I may be missing something because this sounds like an ideal case for conditional formatting. It will allow you to format a cell based on it's own value, or the results of a formula. You can set font, interior, borders and shading using this feature. Let me know if you need any help with it. I would suggest you use it vs vba programming, unless you have 4 or more condition's you need to account for. HTH Cal "Marty" wrote: Greetings: I have a Yes/No pulldown menu in cell F229. My spreadsheet has a white background (color index 0). If "Yes" is selected, I want some text to appear in Cell H229 (the text is currently there, I just want to change it to font color index 0 so that it will be visible), and I want to turn the background of cell J229 yellow and put a border around it. If F229 is selected as "No" or is blank, I want the text in H229 to go invisible (not delete) and reset the yellow bordered cell back to white background with no borders. I recorded and modified a macro. Here is the relevant code (declarations not included): If Target.Cells(1).Address = "$F$229" Then 'Make text in H229 bordered box with yellow background appear if "Yes" in F229 Application.EnableEvents = False If MM.Range("F229") = "Yes" Then Range("H229").Font.ColorIndex = 0 'OK Range("J229").Interior.ColorIndex = 6 'OK Range("J229").Borders(xlEdgeLeft).ColorIndex = xlAutomatic 'OK Range("J229").Borders(xlEdgeTop).ColorIndex = xlAutomatic 'Not executing Range("J229").Borders(xlEdgeBottom).ColorIndex = xlAutomatic 'Not executing Range("J229").Borders(xlEdgeRight).ColorIndex = xlAutomatic 'Not executing Else Range("H229").Font.ColorIndex = 2 'OK Range("J229").Interior.ColorIndex = 0 'OK Range("J229").Borders(xlEdgeLeft).LineStyle = xlNone 'OK Range("J229").Borders(xlEdgeTop).LineStyle = xlNone 'OK Range("J229").Borders(xlEdgeBottom).LineStyle = xlNone 'OK Range("J229").Borders(xlEdgeRight).LineStyle = xlNone 'OK End If Application.EnableEvents = True End If The problem: When I select "Yes" in F229, I only get the LEFT border on the yellow cell. The other three borders don't appear. Any thoughts as to what's going on? Help is appreciated. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Add Borders based on ByVal Target As Range
not sure if this will help or not. maybe you have some other formatting commands
somewhere Range("F229").BorderAround ColorIndex:=1, Weight:=xlThin -- Gary "Marty" wrote in message ... Greetings: I have a Yes/No pulldown menu in cell F229. My spreadsheet has a white background (color index 0). If "Yes" is selected, I want some text to appear in Cell H229 (the text is currently there, I just want to change it to font color index 0 so that it will be visible), and I want to turn the background of cell J229 yellow and put a border around it. If F229 is selected as "No" or is blank, I want the text in H229 to go invisible (not delete) and reset the yellow bordered cell back to white background with no borders. I recorded and modified a macro. Here is the relevant code (declarations not included): If Target.Cells(1).Address = "$F$229" Then 'Make text in H229 bordered box with yellow background appear if "Yes" in F229 Application.EnableEvents = False If MM.Range("F229") = "Yes" Then Range("H229").Font.ColorIndex = 0 'OK Range("J229").Interior.ColorIndex = 6 'OK Range("J229").Borders(xlEdgeLeft).ColorIndex = xlAutomatic 'OK Range("J229").Borders(xlEdgeTop).ColorIndex = xlAutomatic 'Not executing Range("J229").Borders(xlEdgeBottom).ColorIndex = xlAutomatic 'Not executing Range("J229").Borders(xlEdgeRight).ColorIndex = xlAutomatic 'Not executing Else Range("H229").Font.ColorIndex = 2 'OK Range("J229").Interior.ColorIndex = 0 'OK Range("J229").Borders(xlEdgeLeft).LineStyle = xlNone 'OK Range("J229").Borders(xlEdgeTop).LineStyle = xlNone 'OK Range("J229").Borders(xlEdgeBottom).LineStyle = xlNone 'OK Range("J229").Borders(xlEdgeRight).LineStyle = xlNone 'OK End If Application.EnableEvents = True End If The problem: When I select "Yes" in F229, I only get the LEFT border on the yellow cell. The other three borders don't appear. Any thoughts as to what's going on? Help is appreciated. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Add Borders based on ByVal Target As Range
"Border Around" worked like a charm. Thanks for that.
"Gary Keramidas" wrote: not sure if this will help or not. maybe you have some other formatting commands somewhere Range("F229").BorderAround ColorIndex:=1, Weight:=xlThin -- Gary "Marty" wrote in message ... Greetings: I have a Yes/No pulldown menu in cell F229. My spreadsheet has a white background (color index 0). If "Yes" is selected, I want some text to appear in Cell H229 (the text is currently there, I just want to change it to font color index 0 so that it will be visible), and I want to turn the background of cell J229 yellow and put a border around it. If F229 is selected as "No" or is blank, I want the text in H229 to go invisible (not delete) and reset the yellow bordered cell back to white background with no borders. I recorded and modified a macro. Here is the relevant code (declarations not included): If Target.Cells(1).Address = "$F$229" Then 'Make text in H229 bordered box with yellow background appear if "Yes" in F229 Application.EnableEvents = False If MM.Range("F229") = "Yes" Then Range("H229").Font.ColorIndex = 0 'OK Range("J229").Interior.ColorIndex = 6 'OK Range("J229").Borders(xlEdgeLeft).ColorIndex = xlAutomatic 'OK Range("J229").Borders(xlEdgeTop).ColorIndex = xlAutomatic 'Not executing Range("J229").Borders(xlEdgeBottom).ColorIndex = xlAutomatic 'Not executing Range("J229").Borders(xlEdgeRight).ColorIndex = xlAutomatic 'Not executing Else Range("H229").Font.ColorIndex = 2 'OK Range("J229").Interior.ColorIndex = 0 'OK Range("J229").Borders(xlEdgeLeft).LineStyle = xlNone 'OK Range("J229").Borders(xlEdgeTop).LineStyle = xlNone 'OK Range("J229").Borders(xlEdgeBottom).LineStyle = xlNone 'OK Range("J229").Borders(xlEdgeRight).LineStyle = xlNone 'OK End If Application.EnableEvents = True End If The problem: When I select "Yes" in F229, I only get the LEFT border on the yellow cell. The other three borders don't appear. Any thoughts as to what's going on? Help is appreciated. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Add Borders based on ByVal Target As Range
Thanks for the response, Cal. I think what you missed is that I want to
change the formatting of two cells which are cells OTHER THAN the one where I selected the value. I thought (maybe wrongly so) that conditional formatting could only be applied to the cell being changed. Gary's response did the trick. Thanks again. "CBrine" wrote: Marty, I may be missing something because this sounds like an ideal case for conditional formatting. It will allow you to format a cell based on it's own value, or the results of a formula. You can set font, interior, borders and shading using this feature. Let me know if you need any help with it. I would suggest you use it vs vba programming, unless you have 4 or more condition's you need to account for. HTH Cal "Marty" wrote: Greetings: I have a Yes/No pulldown menu in cell F229. My spreadsheet has a white background (color index 0). If "Yes" is selected, I want some text to appear in Cell H229 (the text is currently there, I just want to change it to font color index 0 so that it will be visible), and I want to turn the background of cell J229 yellow and put a border around it. If F229 is selected as "No" or is blank, I want the text in H229 to go invisible (not delete) and reset the yellow bordered cell back to white background with no borders. I recorded and modified a macro. Here is the relevant code (declarations not included): If Target.Cells(1).Address = "$F$229" Then 'Make text in H229 bordered box with yellow background appear if "Yes" in F229 Application.EnableEvents = False If MM.Range("F229") = "Yes" Then Range("H229").Font.ColorIndex = 0 'OK Range("J229").Interior.ColorIndex = 6 'OK Range("J229").Borders(xlEdgeLeft).ColorIndex = xlAutomatic 'OK Range("J229").Borders(xlEdgeTop).ColorIndex = xlAutomatic 'Not executing Range("J229").Borders(xlEdgeBottom).ColorIndex = xlAutomatic 'Not executing Range("J229").Borders(xlEdgeRight).ColorIndex = xlAutomatic 'Not executing Else Range("H229").Font.ColorIndex = 2 'OK Range("J229").Interior.ColorIndex = 0 'OK Range("J229").Borders(xlEdgeLeft).LineStyle = xlNone 'OK Range("J229").Borders(xlEdgeTop).LineStyle = xlNone 'OK Range("J229").Borders(xlEdgeBottom).LineStyle = xlNone 'OK Range("J229").Borders(xlEdgeRight).LineStyle = xlNone 'OK End If Application.EnableEvents = True End If The problem: When I select "Yes" in F229, I only get the LEFT border on the yellow cell. The other three borders don't appear. Any thoughts as to what's going on? Help is appreciated. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Add Borders based on ByVal Target As Range
Marty,
Using the formula options in conditional formatting you can format a cell based on a different cells value. In H229 you would add this condition Formula is =$F$229 = "Yes" then add your format. In J229 you would add this condition Formula is =$F$229 = "Yes" then add your format. This would accomplish the same results without vba coding. Thought I would let you know, your choice of course. HTH Cal "Marty" wrote: "Border Around" worked like a charm. Thanks for that. "Gary Keramidas" wrote: not sure if this will help or not. maybe you have some other formatting commands somewhere Range("F229").BorderAround ColorIndex:=1, Weight:=xlThin -- Gary "Marty" wrote in message ... Greetings: I have a Yes/No pulldown menu in cell F229. My spreadsheet has a white background (color index 0). If "Yes" is selected, I want some text to appear in Cell H229 (the text is currently there, I just want to change it to font color index 0 so that it will be visible), and I want to turn the background of cell J229 yellow and put a border around it. If F229 is selected as "No" or is blank, I want the text in H229 to go invisible (not delete) and reset the yellow bordered cell back to white background with no borders. I recorded and modified a macro. Here is the relevant code (declarations not included): If Target.Cells(1).Address = "$F$229" Then 'Make text in H229 bordered box with yellow background appear if "Yes" in F229 Application.EnableEvents = False If MM.Range("F229") = "Yes" Then Range("H229").Font.ColorIndex = 0 'OK Range("J229").Interior.ColorIndex = 6 'OK Range("J229").Borders(xlEdgeLeft).ColorIndex = xlAutomatic 'OK Range("J229").Borders(xlEdgeTop).ColorIndex = xlAutomatic 'Not executing Range("J229").Borders(xlEdgeBottom).ColorIndex = xlAutomatic 'Not executing Range("J229").Borders(xlEdgeRight).ColorIndex = xlAutomatic 'Not executing Else Range("H229").Font.ColorIndex = 2 'OK Range("J229").Interior.ColorIndex = 0 'OK Range("J229").Borders(xlEdgeLeft).LineStyle = xlNone 'OK Range("J229").Borders(xlEdgeTop).LineStyle = xlNone 'OK Range("J229").Borders(xlEdgeBottom).LineStyle = xlNone 'OK Range("J229").Borders(xlEdgeRight).LineStyle = xlNone 'OK End If Application.EnableEvents = True End If The problem: When I select "Yes" in F229, I only get the LEFT border on the yellow cell. The other three borders don't appear. Any thoughts as to what's going on? Help is appreciated. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Why _Change(ByVal Target As Range) | Excel Programming | |||
| Ranges:Target in Worksheet_SelectionChange(ByVal Target As Range) | Excel Programming | |||
| ByVal Target As Range | Excel Programming | |||
| what does (ByVal Target As Range) mean | Excel Programming | |||
| Many Sub Worksheet_Change(ByVal Target As Range) In One Worksheet | Excel Programming | |||
Linear Mode
