PDA

View Full Version : If Elseif to toggle



jokris
03-23-2019, 10:24 PM
When right clicking within cell range A1 : D4, cell turns green.

I want to expand further on this and add that when right clicking on green cell, cell turns yellow. And when right clicking on yellow, cell turns white.

How would that look like?

And is there a cleaner way by not using Elseif (like switch?)


Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim rInt As Range
Dim rCell As Range
Set rInt = Intersect(Target, Range("A1:D4"))
If Not rInt Is Nothing And Target.Interior.ColorIndex = 0 Then
For Each rCell In rInt
Next
End If
Set rInt = Nothing
Set rCell = Nothing
Cancel = True
Target.Interior.ColorIndex = 4
End Sub

p45cal
03-24-2019, 04:19 AM
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim rInt As Range, rCell As Range
Set rInt = Intersect(Target, Range("A1:D4"))
If Not rInt Is Nothing Then
Cancel = True
For Each rCell In rInt
Select Case rCell.Interior.ColorIndex
Case -4142, 0
rCell.Interior.ColorIndex = 4
Case 4
rCell.Interior.ColorIndex = 6
Case 6
rCell.Interior.ColorIndex = -4142
End Select
Next
End If
End Sub

Fluff
03-24-2019, 07:13 AM
Cross posted https://www.mrexcel.com/forum/excel-questions/1091882-toggle-between-colors-right-click.html

rothstein
03-24-2019, 07:46 AM
Cross posted https://www.mrexcel.com/forum/excel-questions/1091882-toggle-between-colors-right-click.html
For those who might be interested, here is the code I posted over in the MrExcel forum where the OP cross-posted his question (see above quote by Fluff)...



Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A1:D4")) Is Nothing Then
Target.Interior.Color = Split(Trim(Split("16777215 65280 65535 16777215", CStr(Target.Interior.Color))(1)))(0)
Cancel = True
End If
End Sub

MagPower
03-24-2019, 08:55 AM
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A1:D4")) Is Nothing Then
Target.Interior.Color = Split(Trim(Split("16777215 65280 65535 16777215", CStr(Target.Interior.Color))(1)))(0)
Cancel = True
End If
End Sub




Rick

As a fairly new VBAer, can you explain to me how the third statement works (with the two Split functions)? TIA
Russ

大灰狼1976
03-25-2019, 01:07 AM
Since there has been no reply for a long time, let me reply for you.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A1:D4")) Is Nothing Then
Target.Interior.Color = Split(Trim(Split("16777215 65280 65535 16777215", CStr(Target.Interior.Color))(1)))(0)
Cancel = True
End If
End Sub
For ease of explanation, Break it down into the following styles

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim rng As Range
Set rng = Range("A1:D4")
If Not Intersect(Target, rng) Is Nothing Then
Dim s$, colorTgt$, arr, colorTmp$, colorRst&
'Values of three colors,Duplicate values are meant to facilitate the acquisition of next values
s = "16777215 65280 65535 16777215"
'Converting color values to strings, example: green = "65280"
colorTgt = CStr(Target.Interior.color)
'Converting strings into one-dimensional arrays using colorTgt as a delimiter
'example: colorTgt = "65280" ,then arr = array("16777215 "," 65535 16777215")
arr = Split(s, colorTgt)
'example: arr(1) = " 65535 16777215",The subscript of a one-dimensional array starts at 0
colorTmp = arr(1)
'example: colorTmp = "65535 16777215", Trim () Remove the header and tail spaces
colorTmp = Trim(colorTmp)
'Split(colorTmp) = array("65535","16777215"), Split(colorTmp)(0) = "65535"
'example: colorRst = 65535 (yellow), Split () is delimited by space by default when delimiters are omitted
colorRst = Split(colorTmp)(0)
'Colour cell areas use colorRst
rng.Interior.color = colorRst
'Do not pop up the right-click menu
Cancel = True
End If
End Sub

rothstein
03-25-2019, 01:24 AM
Since there has been no reply for a long time, let me reply for you.


Thank you for following up for me. I saw the request but was not sure how to explain my code so that the OP would understand. Your method was perfect and should help the OP understand my code. Thanks again for jumping in.

大灰狼1976
03-25-2019, 05:17 AM
Hi rothstein!
You're welcome:friends:

MagPower
03-25-2019, 11:22 AM
Thanks guys!