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.

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.

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.

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.

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.

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.