VBA: Using Selection.Interior.ColorIndex on multiple columns but only once a line

[Y04NN]

Hi everyone,

I would like to be able to:
- use Selection.Interior.ColorIndex on several columns but only once every line.
- if 1 cell (B5) on the line (5) has already been "colored" (selected) and I select an other cell (D5) one the same line then the the first cell (B5) go back to no color no target.
- avoid using a "snitch" loop that is constantly checking a statement (to keep the macro light and running smoothly) but more in a way that the macro allows you to color the one cell you need and reset other cells on the line (if this make any sense).

here's what I am using now

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'First table




'column a 
 
If Not Intersect(Selection, Range("a1:a20")) Is Nothing Then
With Target
If Selection.Interior.ColorIndex = xlNone Then
Selection.Interior.ColorIndex = 4
Target = 5




Else
Selection.Interior.ColorIndex = xlNone
Target = ""
End If
End With
End If
'column b


If Not Intersect(Selection, Range("b1:b20")) Is Nothing Then
With Target


If Selection.Interior.ColorIndex = xlNone Then
Selection.Interior.ColorIndex = 8
Target = 5
Else
Target = ""
Selection.Interior.ColorIndex = xlNone
End If
End With
End If


'column c
If Not Intersect(Selection, Range("c1:c20")) Is Nothing Then
With Target
If Selection.Interior.ColorIndex = xlNone Then
Selection.Interior.ColorIndex = 6
Target = 5
Else
Target = ""
Selection.Interior.ColorIndex = xlNone
End If
End With
End If


'column d
If Not Intersect(Selection, Range("d1:d20")) Is Nothing Then
With Target
If Selection.Interior.ColorIndex = xlNone Then
Selection.Interior.ColorIndex = 23
Target = 5
Else
Target = ""
Selection.Interior.ColorIndex = xlNone
End If
End With
End If

This is an example from a larger macro I use. Target 5 help me for calculations.

I've been searching on the forum and online ways to meet my "user" requirements but I couldn't find anything helpful.
I guess it's kinda specific ... Please bear with me I've never used this function and I am fairly new to VBA macros.
Please let me know if I need to add more info or if my explanation isn't clear.



Any info or comment will help me improve :D

Thank you for your time.

 

[JackDanIce]

Welcome to the board. Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Dim ColourArrIndex() As Variant
    ColourArrIndex = Array(4, 8, 6, 23)
        
    If Target.Count = 1 And Not Intersect(Selection, Cells(1, 1).Resize(20, 4)) Is Nothing Then
        With Selection
            .Interior.ColorIndex = IIf(.Interior.ColorIndex = xlNone, ColourArrIndex(Target.Column - 1), xlNone)
            .Value = IIf(.Interior.ColorIndex = xlNone, vbNullString, 5)
        End With
    End If
    
    Erase ColourArrIndex

End Sub

 

[Y04NN]

Thank you JackDanIce!

I've tried (both the first version and the edit) I can't make it work.

They're 80 % chance that the problem is located between my keyboard and my chair ^^"


I am not sure where I should write it... That might be my problem

I tried different ways (pasted it for every column or at the end after all columns). I probably didn't merge them correctly.

The current result I got is:

- one cell selection is not available anymore (selection has to be at list two cells).
- I can still select A1:A2 then B2:B3 and have A2 and B2 colored.

 

[JackDanIce]

I do not understand your explanation of the problem.

The code applies for a single cell (only) selected within A1:D20. Are you saying it should be based on a selection of 2 cells?

 

[Y04NN]

No problem I'll try to express myself better.

Sum-up:
The code that I am using at the moment helps filling "templates" files quickly by selecting and un-selecting cells with mouse click. Can be one cell with on click or can be several cells with one click going down the column.
Column A to D have different color index. The number 5 in cells helps for calculation. See letter A on the picture.

For my file to work better but also in the logic of what the file is for, only one cell per raw can be colored. Some raw can stay empty.

I would like to be able to:
- use Selection.Interior.ColorIndex on several columns but only once every line.
- if 1 cell (B5) on the line (5) has already been "colored" (selected) and I select an other cell (D5) one the same line then the the first cell (B5) go back to no color no target.
- avoid using a "snitch" loop that is constantly checking a statement (to keep the macro light and running smoothly) but more in a way that the macro allows you to color the one cell you need and reset other cells on the line (if this make any sense).

picture

Currently:
Let's say I am selecting B13:B14 then D14:D15 see letter C on the picture. I have two cells colored.
Instead I'd like the code to reset B14 when selecting D14 like the "theoretical" example on letter D.

Target:
In my example with Letter D on the picture, I pretend to select B18:B19 then D19:20. In that case the code would reset D19 to only allow one cell per raw.



@JackDanIce,

I am not sure I use the code you advised correctly. I am not sure where to implement it in my existing code (Before, After, for every "column-selection-range" parts).

But what I noticed , is that I can't select one cell any longer like letter B on the picture using your code. Now it has to be at least a 2-cell selection in order to get color in my selections. And I am still able to select two cells on one raw like letter C on the picture.

This what I meant when I wrote:

The current result I got is:

- one cell selection is not available anymore (selection has to be at list two cells).
- I can still select A1:A2 then B2:B3 and have A2 and B2 colored.

​

Am I making more sense ^^" ?

Please let me know. At this point it's an English issue :/ rather than my VBA lack of knowledge issue ^^

 

[JackDanIce]

I think I understand... replace ALL of your code with below and try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Dim ColourArrIndex() As Variant
    ColourArrIndex = Array(4, 8, 6, 23)
        
    If Not Intersect(Selection, Cells(1, 1).Resize(20, 4)) Is Nothing Then
        With Selection
            With Cells(1, .row).Resize(, 4)
                .Interior.ColorIndex = xlNone
                .Value = vbNullString
            End With
            .Interior.ColorIndex = ColourArrIndex(Target.Column - 1)
            .Value = 5
        End With
    End If
    
    Erase ColourArrIndex

End Sub

 

[Y04NN]

Let me know what isn't clear otherwise I won't be able to be clearer.

Tried your code on its own.

feedback:

1) Code shorter (great!), works almost the same as mine (see 2).
2) Can't select multiple cells on one click anymore (meaning: click A1 hold the click and go down the column with the mouse to A10 then realize the button > helps having A1:A10 colored one click one selection) This is not possible anymore but has to be).
3) The key function I am trying to get, be able to click only on cell on each raw, is either not there or not working.

 

[JackDanIce]

You should be able to select more than one cell, I changed the code after I realised it had Target.Count = 1 in it.

 

[Y04NN]

IMPROVEMENT ! XD

Feedback:

- I am able to hold and select multiple cells on one click again.
- I am not able to put the cell back to: empty & no color, by re-selecting them (In case it's a raw or suits of raws that need to be left blank and I already colored them by mistake, I have no way of correcting it anymore)
- The "only-on-cell-per-raw" function works on raw A only.

 

[JackDanIce]

Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Dim ColourArrIndex() As Variant
    ColourArrIndex = Array(4, 8, 6, 23)
        
    If Not Intersect(Selection, Cells(1, 1).Resize(20, 4)) Is Nothing Then
        With Selection
            .Interior.ColorIndex = IIf(.Interior.ColorIndex = xlNone, ColourArrIndex(Target.Column - 1), xlNone)
            .Value = IIf(.Interior.ColorIndex = xlNone, vbNullString, 5)
        End With
    End If
    
    Erase ColourArrIndex

End Sub