Highlighting a single cell

[Jaymond]

Hello everyone! First time on the site, and I think this is a great forum. I've spent the last hour searching through past threads hoping to find an answer to my question, but am at a loss.

What I would like to do is have the cell that I currently have selected to be highlighted with whatever color I choose. I don't want to do the banding whereby the entire row and column is highlighted to point me to the cell.

For example, I want any cell that I click on in the sheet will fill in with red color. Then when I click off of that cell it goes back to white and the next cell I click on will fill in with red. I only want the one cell I have selected to be highlighted - not the entire row and column like the color banding solution.

Any ideas? Thanks so much!

 

[VoG]

Right click the sheet tab, select View Code. Copy and paste in the following:

Public LastCell As Range

Private Sub Worksheet_Activate()
Set LastCell = ActiveCell
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Interior.ColorIndex = 3
LastCell.Interior.ColorIndex = 0
Set LastCell = Target
End Sub

 

[Richard Schollar]

Hi Jaymond

Welcome to the Board!

You can do this via Conditional Formatting and by utilising VBA events.

1. Select the area of the sheet you want this to apply to and go Format>Conditional Formatting

2. Change to Formula Is and type the following formula into the formula box:

=AND(CELL("row")=ROW(),CELL("col")=COLUMN())

3. Select your desired format (ie red pattern) and click OK.

4. On the tab that you have selected, right click and select "View Code"

5. Paste the following into the code pane of the VBE that will open up:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = True
End Sub

6. Shut down the VBE and you should now have the CF working as you specified.

Best regards

Richard

 

[Jaymond]

VoG - I had some problems with your method. Kept getting errors asking me to debug.

Richard - Your method worked perfectly! Thank you.


I have one other question.....

For cells with text that have hyperlinks: You know how the text color changes after you click the hyperlink? It changes the text color to that light purple-pinkish color to let you know that you've clicked the link before. Well, I don't want the text color to change. How do I prevent the text color from changing after I click on the hyperlink?

Thanks again!

 

[Jaymond]

OK - one more question, I sear, hehehe.

I have an embedded object (it's a PDF file). When I click on the object, I want all the cells behind the object to fill in with red color. Then when I click off the object to another cell, I want all the cells behind it return to white. Is this even possible?

 

[Richard Schollar]

One way around the hyperlinks color change issue is to format the font color of the cell to whatever you want (eg Blue) rather than Automatic (which is the default), then the hyperlink color won't change when it's clicked.

Richard

 

[Jaymond]

One way around the hyperlinks color change issue is to format the font color of the cell to whatever you want (eg Blue) rather than Automatic (which is the default), then the hyperlink color won't change when it's clicked.

Richard

Hmmm, that doesn't seem to work. In fact, that is exactly my issue. I have the text in the cell set to blue, and when I click on the link it changes it to that purpleish-pinkish color.

It seems your work around works for every single color other than blue, LOL.

 

[Richard Schollar]

Try applying the font color after the hyperlink has been placed in the cell.

Richard


EDIT: No, this doesn't work - you're right, every color as long as it isn't the default. D'oh!