Conditional Formatting borders around cells with formulas

[oam]

I am trying to add conditional formatting borders and to highlight every other row around all cells containing data (A3:N100) but the conditional formatting see the formulas in the cells and adds borders around the entire range (A3:N100).Is there a way to add conditional formatting borders and to highlight every other row around cells only containing data?

Thank you for any and all help I am running Excel 2007

[ashleymoore]

Slightly re-jigging some old code which I used to spot cells with formulae, instead of selecting them, I added a border (which is what you seem to want). Its no beautiful code, but works. Just don't select the entire sheet, 'cos it'll go through every cell to check them all.


Sub HasFormulas2()
Dim Addrss As String
If TypeName(Selection) <> "Range" Then
MsgBox "Please select a valid Range to use this Function", vbOKOnly, "Cool Tools !!"
Exit Sub
End If
Select Case Selection.HasFormula
Case True
MsgBox "All cells in the selected range have formulas", vbOKOnly, _
"Does this range have formulas ?"
Case False
MsgBox "All cells in the selected range do not have formulas", vbOKOnly, _
"Does this range have formulas ?"
Case Else
' MsgBox "Some of the cells in the selected range have formulas", vbOKOnly, _
' "Does this range have formulas ?"
Addrss = ""
For Each sell In Selection
If sell.HasFormula Then
If Addrss = "" Then
Addrss = sell.Address
Else
Addrss = Addrss & "," & sell.Address
End If
End If
Next
Range(Addrss).Borders.LineStyle = xlContinuous
End Select
End Sub








I am trying to add conditional formatting borders and to highlight every other row around all cells containing data (A3:N100) but the conditional formatting see the formulas in the cells and adds borders around the entire range (A3:N100).Is there a way to add conditional formatting borders and to highlight every other row around cells only containing data?

Thank you for any and all help I am running Excel 2007

[oam]

ashleymoore,

Thank you your response but it is not what I was looking for, I had to use another method.

I do appreciate you responding, thank you :yes

[ashleymoore]

It'd be good to know the approach you used. Can you outline please?
thanks,
Ashley

[oam]

I used code to copy values from one worksheet to another. This allowed the conditional formatting to work properly due to no formulas in the cells.Hope this helps.