Highlighting a single cell

Jaymond

New Member
Joined
Sep 17, 2006
Messages
14
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!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Right click the sheet tab, select View Code. Copy and paste in the following:

Code:
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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:

Code:
=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:

Code:
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

New Member
Joined
Sep 17, 2006
Messages
14
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

New Member
Joined
Sep 17, 2006
Messages
14

ADVERTISEMENT

OK - one more question, I sear, hehehe. :devilish:

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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

New Member
Joined
Sep 17, 2006
Messages
14
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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!
 

Forum statistics

Threads
1,141,098
Messages
5,704,319
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top