# 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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### 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
Very quick reply Kristy. Thank you so much for your concern.
Hope somebody else find why that happen.
rgds,
Arnolf Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,004
Messages
5,834,833
Members
430,324
Latest member
bosphoruskid ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back