Solved

Toggle Interior.ColorIndex using mouse clicks

Posted on 2013-10-22
2
226 Views
Last Modified: 2013-10-22
I have found the following code that changes the colour of a cell when clicked to Red

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then Target.Interior.ColorIndex = 3
End Sub

This is fine, but what happens if I click the wrong cell?

What code would do the following:

Click a single cell
Check the cell has [No Fill]
If [No Fill] then fill [Red]
If [Red] then reset to [No Fill]

I think I have the logic of what I want to do, but not the knowledge of the code required.
Which is where you experts come in.

Thanks for your time

Neil
0
Comment
Question by:NELMO
2 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 39590650
Hello Neil,

try this:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count = 1 Then
    If Target.Interior.ColorIndex = 3 Then
        Target.Interior.ColorIndex = 0
    Else
        If Target.Interior.ColorIndex = -4142 Then
            Target.Interior.ColorIndex = 3
        End If
    End If
End If

End Sub

Open in new window

What it does:
If you select a cell and it has no fill, it will be filled with red. If a selected cell already has a colour fill other than red, the fill colour will not be changed. If the cell has a red fill, the red fill will be removed.

Copy the code. Right-click the worksheet tab, select "View Code" and paste the code into the code window.

cheers, teylyn
0
 

Author Closing Comment

by:NELMO
ID: 39590947
Excellent Teylyn

that is exactly what I want.

Thanks

Neil
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question