Conditional Format Selected Cell

[Pacman52]

Just out of interest is it possible to format a cell when a user clicks in it to enter a value and once they move to the next relevant cell the formatting goes.

Basically, I designing a 'form' on a worksheet and want to highlight whatever cell the user clicks and is entering a value in, (so that its obvious what information is required in a particular cell).

I've already locked all the other cells.

Logically I'm thinking this must be achievable and if it is what would the CF formula be?

Thanks for any replies. Paul

 

[Joe4]

That would typically be done with VBA, not CF.
See here for how to do that: Highlight the Active Cell, Row, or Column

 

[Pacman52]

Wow that has to be the quickest response ever !!

Thanks so much, Joe I'll have a read through the link you provided.

 

[Joe4]

You are welcome!

 

[Pacman52]

Ok so that sub obviously works the way it is intended to but it doesn't do what I'm after which is to highlight whatever cell a user has selected. I tried to play around with the code and adjust it, which sort of worked until I locked some cells.

I did a bit of a search online and came across this formula to use with conditional formatting which works although the CF only applies as soon as something has been typed in the a cell.

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

Any ideas on anything I could change in the formula to make it work when a cell is actually selected?

Thanks Paul

 

[Joe4]

No, I do not know of any way to do it with Conditional Formatting. I don't know if it is possible.

Ok so that sub obviously works the way it is intended to but it doesn't do what I'm after which is to highlight whatever cell a user has selected

That code should do PRECISELY that, highlights whatever cell you select.

I tried to play around with the code and adjust it, which sort of worked until I locked some cells.

When you protect your sheet, check the option to allow them to change the format of cells, and it should work:

 

[Pacman52]

Thanks so much for the replies and the tip on protecting a worksheet and a tick in Format Cells. I've never really noticed at all the options on this list but now you've highlighted it, I can see myself using some of them later.

The CF formula I posted earlier does actually do what I was trying to achieve now, I only had the issue whilst testing it on the actual sheet I was working on but as soon as I went to another sheet and then back to the 'test' sheet the CF formula worked perfectly.

Thanks again though for your help and advice.

Paul

 

[Joe4]

You are welcome.
Glad you were able to get something that works like you want!