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.
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