Highlight cells

[lighting77]

Hi.
I need to highlight cells from active cell, vertically, horizontally, diagonally and anti diagonally in an excel sheet. These highlights move automatically with an active cell.
Could some one help. Thanks

 

[JamesCanale]

This gets sluggish. The conditional formatting is fast, but that won't change the formatting until you activate a cell in some way. So I made a worksheet macro that performs a meaningless change on the selected cell - but it slows down performance.

MrExcelPlayground22.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Sheet13

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:Y25	Expression	=ROW(A1)-COLUMN(A1)=CELL("row")-CELL("col")	text	NO
A1:Y25	Expression	=ROW(A1)+COLUMN(A1)=CELL("Row")+CELL("Col")	text	NO
A1:Y25	Expression	=OR(ROW(A1)=CELL("row"),COLUMN(A1)=CELL("col"))	text	NO

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim KeyCells As Range

    Set KeyCells = Range("A1:Y25")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

Target.Value = Target.Value

End If
End Sub

 

[JamesCanale]

I just saw this same problem this morning in linked-in of all places. They had a better approach on the VBA code:

Application.Calculate

instead of target.value=target.value

 

[lighting77]

Hi bro, I have no idea about vba codes but I’ll have a crack at it.

 

[JamesCanale]

In that case, after doing the conditional formatting, right-click on the sheet tab on the bottom, and 'view code'.

paste this bit in on the right:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub