Add Borders based on ByVal Target As Range

[Guest]

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.

 

[Guest]

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

 

[Gary Keramidas]

not sure if this will help or not. maybe you have some other formatting commands
somewhere

Range("F229").BorderAround ColorIndex:=1, Weight:=xlThin

 

[Guest]

"Border Around" worked like a charm. Thanks for that.

 

[Guest]

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.

 

[Guest]

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