# Conditional Formatting dynamic?. SelectionChange

#### Arnolf

##### Board Regular
Hello,

How can insert a formula in the conditional formatting depending on the number row every time I select a cell?
I mean, every time I select a cell in the range E2:E10, the formula should change in the conditional formatting for the range A2:A14.

Example:
If I select E2, the Formula is in the Conditional Formatting for the range A2:A14 should be:
=(A2<>"")*(COUNTIF(A\$2:A2,A2)<=COUNTIF(F\$2:J\$2,A2))
If I select E3, the Formula is in the Conditional Formatting for the range A2:A14 should be:
=(A2<>"")*(COUNTIF(A\$2:A2,A2)<=COUNTIF(F\$3:J\$3,A2))
If I select E4, the Formula is in the Conditional Formatting for the range A2:A14 should be:
=(A2<>"")*(COUNTIF(A\$2:A2,A2)<=COUNTIF(F\$4:J\$4,A2))
and so on for the other cells. The variable is the number of the active row.

The range E2:E10 is only an example. My real range will be E2:E1500.

I tried to do it with the next code. Of course, it is wrong and doesnt work (just to give an idea, what I need to do).
Is it a better way to do it?
rgds,
Arnolf.

Code:
``````Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim b As Long
If Intersect(Target, Range("E2:E10")) Is Nothing Then
Else
b = Target.Row
End If
If Not Intersect(Target, Range("E2:E10")) Is Nothing Then
Range(Target.Offset(0, -4), Target.Offset(12, -4)).FormatConditions.Delete
If Not IsEmpty(Target) Then
Range(Target.Offset(0, -4), Target.Offset(12, -4)).FormatConditions.Delete
Range(Target.Offset(0, -4), Target.Offset(12, -4)).FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(A2<>"")*(COUNTIF(A\$2:A2,A2)<=COUNTIF(F\$" & b & ":J\$" & b & ",A2))"
Range(Target.Offset(0, -4), Target.Offset(12, -4)).FormatConditions(1).Interior.ColorIndex = 33
End If
End If
End Sub``````

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### Von Pookie

##### MrExcel MVP
I'm not sure if this is what you're wanting, but it works for me:

Code:
``````Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("E2:E1500")) Is Nothing Then
With Range("A2:A14")
With .FormatConditions
.Delete
"=(\$A\$2<>"""")*(COUNTIF(\$A\$2:\$A\$2,\$A\$2)<=COUNTIF(\$F\$" & Target.Row & ":\$J\$" & Target.Row & ",\$A\$2))"
End With
.FormatConditions(1).Interior.ColorIndex = 33
End With
End If

End Sub``````

If I tried it without the absolute references in the formula, it was putting some weird ranges in. Added the '\$' signs and worked right away.

#### Arnolf

##### Board Regular
Thank you so much Kristy!!!!
It certainly works.
Only I have a little problem with the absolute references
"=(\$A\$2<>"""")*(COUNTIF(\$A\$2:\$A\$2,\$A\$2)<=COUNTIF(\$F\$" & Target.Row & ":\$J\$" & Target.Row & ",\$A\$2))"

I changed to:
"=(\$A\$2<>"""")*(COUNTIF(\$A\$2:\$A2,\$A2)<=COUNTIF(\$F\$" & Target.Row & ":\$J\$" & Target.Row & ",\$A2))"
But, doesnt work.
Any way to solve it?.

This is what I am trying to achieve:

If the cursor is in E2
cformat.xls
ABCDEFGHIJ
1
2116646
328233
43191666
54151239
65101234
760
860
970
Sheet1

If the cursor is in E3,... and so on for the others
cformat.xls
ABCDEFGHIJ
1
2116646
328233
43191666
54151239
65101234
760
860
970
1060
119
123
1311
Sheet1

#### Von Pookie

##### MrExcel MVP
Yeah, that's what it was doing to me--if I didn't have the absolute references in, it was using references of IS65534, etc. in the formula.

I have no idea why

#### Arnolf

##### Board Regular
Thank you so much for your concern.
Hope somebody else find why that happen.
rgds,
Arnolf

Replies
3
Views
85
Replies
9
Views
79
Replies
12
Views
167
Replies
6
Views
196
Replies
9
Views
146