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.

 

[Y04NN]

Improvement ! :D

Feedback:

- I am able to select and un-select multiple cells on one click again.
- The "only-on-cell-per-raw" function isn't there yet.


Your 510-character code replace my 1422-character one. ^^ I definitely have room for improvement !

 

[JackDanIce]

Raw or row? Last attempt as it's still not clear, if this doesn't work, someone else can correct:

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
            Cells(.row, 1).Resize(, 4).ClearContents
            .Interior.ColorIndex = IIf(.Interior.ColorIndex = xlNone, ColourArrIndex(Target.Column - 1), xlNone)
            .cells(1,1).Value = 5
        End With
    End If
    
    Erase ColourArrIndex

End Sub

 

[Y04NN]

OOOOh I am soooo sorry! I meant ROW. I kept writing raw while meaning ROW.

I can see how I got you confused, sorry.

Plus, as a new member I apparently can't Edit my post, so i'll keep having everyone confused with my miss spelling ...

My feed back should have been:

Feedback:
- I am able to select and un-select multiple cells on one click again.
- The "only-on-cell-per-ROW" function isn't there yet.

Tried your last code. It is almost what I meant!

With your latest code:
- If select A1 (one click) then select B1 (on click), the value A1 go back to no value after I click B1. B1's value become 5 but it does not turn the color back to blank/no color (as it should). A row has to be either all blank without any value in any cell or one cell colored with 5 as a value in this particular cell.

- Now your code only write 5 in the first cell of multiple selection:

ex:if I select with one click A1 to A5, color will be applied to A1 to A5 but the value 5 will only be in cell A1 instead of being in cells A1 to A5. Every time I have a color I need the value "5" to be in the cell. Every time the cell is blank the value has to be "" (no value).

Also, with your current code, if I want to correct a "selection" error, let's say one click selection C2 to C5, instead of C2 to C4. I should correct it by one click selection C4 to C5, the color turn back to blank (like it should) but keep the value "5" in the first cell "un-selected" C4 instead of no color, no value.
​

- I can still have multiple colors on one row which shouldn't be possible in the way I need the file to be used.



So let me write a scenario to make sure I am clear.


I have my template to fill.

Normal filling situation:
I select A1 to A4 with one click selection.

(implies each cell from A1 to A4 has a value ="5" and has been colored).​

Then D5 to D10 with on click selection.

(implies each cell from D5 to D10 has a value ="5" and has been colored).​

overlapping situation:
I select C10 to C18 with one click selection.

(Each cell from C10 to C18 has a value ="5" and has been colored. On row 10 there should be only one cell that has a value and that is colored. D10 and C10 shouldn't be both colored and have value = "5". Code should make cell D10 go back to: no value, no color.)​

correcting situation:
I realize I should have stopped at C14 instead of C18 while doing my selection.
I select C15 to C18 with one click selection.

(implies C15 to C18 go back to no color no value)​

Is it clearer :S ? Please let me know what's bugging you if not.


Thank you for your patience JackDanIce !

 

[JackDanIce]

As mentioned I'll let someone else take over, you're not appreciating the fact I can't see your PC monitor so it's all guessed on the descriptions you provide, which for me are overly worded and not precise enough I'm afraid.

I think you're trying to combine multiple rules into a single solution but I can't figure out what those rules distinctly are or with such a small range area, why you're insisting on using more than one range at a time.

Finally I think some rules are for a Worksheet_SelectionChange event and others maybe for Worksheet_Selection event so again it's not clear where to include what and I don't want to keep making assumptions as to what you actually want.