PDA

View Full Version : [SOLVED] Dynamic range help for conditional formatting rows



steveo411
10-26-2018, 02:43 PM
Hi.... I would like help with the following

I would like to create a macro that will add a top border to a row (from A:Z)if the cell in column E is not empty. The coding would only have to check down 200-250 lines.

The only other way I can explain it is to use an Excel Formula:

IF E14 <> "", add top border, then check the next row
IF E15 <> "", add top border, then check the next row
IF E16 <> "", add top border, then check the next row
IF E17 <> "", add top border, then check the next row
etc......

Thanks

p45cal
10-26-2018, 03:02 PM
Would conditional formatting by itself do it?
Let's say you wanted to show the top border form row 3 to 250, columns A:Z.
Select A3:Z250 (making sure that row 3 has the active cell in it)
Go into conditional formatting, choose Use a formula to to determine which cell to format, use the formula:
=$E3<>""
choose your top border format, OK, OK.
Job done. See attached .xlsx file

Else a macro:
Sub blah()
For Each cll In Range("E3:E250").Cells
With Cells(cll.Row, 1).Resize(, 26).Borders(xlEdgeTop)
If cll.Value <> "" Then .LineStyle = xlContinuous Else .LineStyle = xlNone
End With
Next cll
End Sub
See attached .xlsm file, sheet2 and its button.

steveo411
10-26-2018, 03:21 PM
It needs to be a VBA, as the excel workbook/worksheet is created from a third part application and no 2 .xlsx books are the same. I have create a workbook to store a vba to run all the other fun things to make the sheets all "look" the same, and this is one of the last steps I need to make automated! I will try your coding and let you know!

EDIT: IT works perfect! Thank you!