Format cells in range based on value in cell below?

[TheWennerWoman]

Hello,

I'm trying to find a quick way to avoid having to manually do this.

I have a row of cells, for example, A894 - FF894. What I'd like to do is look at A895 and if it isn't blank then in cell A894 add a double bottom border. Then move onto cell B895, if it isn't blank then add a double bottom border to B894. And so on through the range.

Any help appreciated and thank you for reading.

 

[My Aswer Is This]

You said: What I'd like to do is look at A895
Do you mean select?
No script can run when you just look at a cell and does the formatting stay in the cell when you select the next range or do you only want the formatting when the cell is the active cell?

 

[Marc L]

Hello, a VBA demonstration for starters :​

Sub Demo1()
        Dim Rc As Range
        Application.ScreenUpdating = False
    For Each Rc In [A895:FF895]
        If Not IsEmpty(Rc) Then Rc.Borders(8).LineStyle = xlDouble
    Next
        Application.ScreenUpdating = True
End Sub

 

[Toadstool]

Hi TheWennerWoman,

I'm not sure if this is what you want, and you can only format a single line border, so here's my attempt using Conditional Formatting.

TheWennerWoman.xlsx
	A	B	C	D	E
894	x
895	x		x		x
896
897	x			x
898			x
899
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A894:FF999	Expression	=A895<>""	text	NO

 

[TheWennerWoman]

Sub Demo1() Dim Rc As Range Application.ScreenUpdating = False For Each Rc In [A895:FF895] If Not IsEmpty(Rc) Then Rc.Borders(8).LineStyle = xlDouble Next Application.ScreenUpdating = True End Sub

Excellent, many thanks, works a treat!