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!

 

[Peter_SSs]

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.

Are you saying that now if you click on various individual cells on that worksheet (without going to another worksheet in between) that the conditional formatting highlights each cell as you select it and removes the highlight from the previously selected cell? (That would not be normal behaviour unless something else is at play)

Do you have any vba at all in the workbook?

 

[Pacman52]

Hi Peter sorry for not responding sooner, I just noticed the post.

I do have VBA in the actual workbook but nothing that relates to the CF. I basically just used and adapted a formula (as below) and it seems to work ok so far during testing.

To be honest this was only put into the worksheet as a visible 'guide' to the user to ensure they are entering the correct data into a specific cell, if you think it could cause problems later on I'm more that happy to remove it.

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

I'd love to hear your thoughts on this.