CODEWITHSUNDEEP
vba
Darragh Lynch
Darragh Lynch

Reputation: 43

Coloring Cells on Microsoft Excel on Tap/Touch

I am trying to create VBA script that will allow me to click or tap on a cell in Microsoft Excel 2013 on a tablet/desktop and that cell (in column F) will change color to green.

Then I want the same functionality on the cell next to it (in column G) so that It can change to Red.

The idea is that the cells in column F are 'Yes' Answers to questions that when tapped light up green and the cells in Column G are 'No' Answers to questions that when tapped light up red instead.

So far I have the code written to allow me to light up the cells in column F green but I am unsure how to go about this with column G as I have not written much VBA script before.

Here's my Code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Cancel = True
Worksheet_SelectionChange Target

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub

    'if cell fill is Blank, change to Green
    If Selection.Interior.Color = RGB(255, 255, 255) Then
        Selection.Interior.Color = RGB(50, 200, 50)
        GoTo Passem


    'if cell fill is Green, remove fill color
    ElseIf Selection.Interior.Color = RGB(50, 200, 50) Then
            With Selection.Interior
            .Pattern = x1None
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With



ElseIf Intersect(Target, Range("G:G")) Is Nothing Then Exit Sub

    'if cell fill is Blank, change to Red
    If Selection.Interior.Color = RGB(255, 255, 255) Then
        Selection.Interior.Color = RGB(250, 20, 20)
        GoTo Passem

    'if cell fill is Red, remove fill colour
    ElseIf Selection.InteriorColor = RGB(250, 20, 20) Then
        With Selection.Interior
            .Pattern = x1None
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End If
End If

Passem:

End Sub

Upvotes: 0

Views: 848

Answers (1)

user3598756
user3598756

Reputation: 29421

I'd go like follows to let your code handle future enhancements more easily:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count = 1 Then HandleCellColor Target '<--| go on only if one cell is selected 
End Sub

Sub HandleCellColor(rng As Range)
    Select Case rng.Column '<--| check what column you're dealing with and act accordingly
        Case 6 ' column "F"
              SetCellColor rng, RGB(50, 200, 50) 
        Case 7 ' column "G"
              SetCellColor rng, vbRed '<--| it's easier to reference vba colors enumeration,  if they suit you
     End Select
End Sub 


Sub SetCellColor(rng As Range, rgbColor As Long)
    With rng.Interior
        If .Color = vbWhite Then '<--| vbWhite is equivalent to RFB(255, 255, 255)
            .Color = rgbColor 
        ElseIf .Color = rgbColor Then
            .Color = vbWhite
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade =  0
         End If
    End With 
End Sub

Where I also added a line to color back to white the background of a cell that is selected or doubleclicked being already colored with the passed color

Upvotes: 0

Related Questions