Macro formats field with value

[FrankM]

This may be kindda easy but for some reason I can't figure it out.

I have a Macro that formats multiple sheets within the same workbook.
Everything is working great but there is one last step I'd like to do and I'm
not certain on how to accomplish it.

If the Cell has contents I would like the Cell to have a border on all four
sides but only if the Cell has some content, any content, a number, text,
anything, it doesn't really matter. I'm having difficulties getting this into
a Macro.

Any suggestions would be great. Thank you!

 

[sebastienm]

Hi,
You can use COnditionalFormatting feature to set a general conditional
format to the entire range.
Eg: range A1:A100
''' -----------------------------------------------
Sub SetCondFormat()
Dim rg As Range

Set rg = Range("A1:A100")
rg.Select

With rg.FormatConditions

.Delete
.Add Type:=xlExpression, Formula1:= _
"=" & rg.Cells(1).Address(False, False) & "<>"""""

With .Item(1).Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Item(1).Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Item(1).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Item(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
End Sub
'''-----------------------------------------

 

[Charles Chickering]

Frank, a macro isn't even needed for this. Conditional formatting will do the
trick, and you should be able to use the macro recorded to get your macro to
automatically add the conditional formatting. Here are the steps to make a
selection of cells, assuming the top left selected cell is A1, have a border
if the cell is not empty:

Select Cells
Goto: Format... Conditional Formatting
Select the "Cell Value Is" Drop down box and choose "Formula Is"
Type this in the TextBox: =A1<>""
Click the "Format" button
Select the "Border" tab
Choose the border you wish to have if the cell is not empty
Click "Ok"
Click "Ok" again

That's it. Let me know if you have problems or need helping getting the
macro recorder to work with this.