VBA Pivot Table Dynamic Range & Only One Selection per Row

[hglymph]

I am pretty adept at Excel and pivot tables, but am a beginner when it comes to VBA coding. I have a pivot table that has data values in a certain range of the table, say B6 thru N100 at most. Based on slicer selections the rows that actually show visible values can be anywhere from 10 rows to 100 rows. What I am trying to do is change cell fill color on cells that I click/change, to yellow. I have scoured the internet to find the following code....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
If .Interior.ColorIndex = 6 Then
.Interior.ColorIndex = xlColorIndexNone
Else
.Interior.ColorIndex = 6
End If
End With

End Sub

......which works great to color the cells when clicked, but I have a couple of issues I can't seem to figure out. Again, I am new to VBA.

1. How do I set the range of cells the above code applies to? Right now, it applies to the whole sheet which I don't want. At most B6 thru N100.

2. Based on slicer selections, visible rows will vary (N100 at most). Is there a way to set a dynamic range that goes to the last visible row of data?

3. Lastly, I have 13 columns of data ( B thru N). I want the above to only allow at most, one yellow highlighted cell per row (some rows may have no yellow cells). For example, if I click cell C10 and change it to yellow, if I click F10, if should change F10 to yellow, and revert C10 back to the initial fill color (no fill). Is there a way to do this?

I want to keep the ability to click a yellow cell again to revert it back to no fill color. Thank you to anyone who has any ideas and are willing to impart your knowledge and wisdom to me.

 

[DanteAmor]

Welcome to the MrExcel forum. Please accept my warmest greetings

Instead of a macro use conditional formatting

1. Create a new conditional formatting rule.

2. On the Home tab, in the Styles group, click Conditional formatting > New Rule… In the New Formatting Rule window, select Use a formula to determine which cells to format.
Enter the formula in the corresponding box.

Excel Formula:

=AND(ROW()=CELL("row"),COLUMN()=CELL("col"))

3. In Applies to:
=$B$6:$N$100

4. Click the Format… button to choose your custom format. Fill yellow color

5. Put the following code in the events of your sheet. (Replace your code)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.ScreenUpdating = True
End Sub

Return to the sheet and select any cell, inside or outside the specified range to see the result.




Note:

Perform the following test instead of AND in the formula, use OR so you can see the results.​

=OR(ROW()=CELL("row"),COLUMN()=CELL("col"))​

 

[hglymph]

Thank you Dante. I tried the conditional formatting as well as the VBA (AND and OR), and the only issue I can see right now is that the sheet doesn't keep the other yellow cells in the sheet. So every time a click another cell, it makes that cell yellow and clears the previous cell. I only want that to happen if more than 1 cell in a row is clicked, only one of them in the row can be yellow. If not in the same row, I want to be able to click on any other cells and any of them should stay yellow unless I click the yellow cell again, or if I click another cell in the row where one cell is already yellow. Thoughts?

 

[DanteAmor]

I only want that to happen if more than 1 cell in a row is clicked, only one of them in the row can be yellow. If not in the same row, I want to be able to click on any other cells and any of them should stay yellow unless I click the yellow cell again, or if I click another cell in the row where one cell is already yellow.

I misunderstood what you need.
Get rid of the conditional formatting.

Try the following code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim lr As Long
  
  lr = Range("B:N").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  If Not Intersect(Target, Range("B6:N" & lr)) Is Nothing Then
    With Target
      If .Count > 1 Then Exit Sub
      If .Interior.ColorIndex = 6 Then
        .Interior.ColorIndex = xlColorIndexNone
      Else
        Range("B" & .Row & ":N" & .Row).Interior.ColorIndex = xlColorIndexNone
        .Interior.ColorIndex = 6
      End If
    End With
  End If
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --