Conditional Format help please

[chef]

Hi there
I am trying to find a resolution in light of my organisation disabling VBA macros and wont allow me the unblock feature so causing a few operational issues and a real pain.
In this case I have a range of cells from c13 to c200 with employee numbers. I am wanting to be able to click on any one of these cells and have the background color and font to change to help user identify number clearer.
Is this possible without VBA. I have tried a helper col in B to try and enter a 1 indicator but cant get that to work either.
Any help would be appreciated and I am sure quite a few users will be frustrated with having macros disabled if organisation policy

regards
Ian

 

[6StringJazzer]

Here is a sample file that shows how to do this with conditional formatting, no VBA

Loading Google Sheets

 

[chef]

Thank you for your help.
Sorry to be basic but i click on loading google sheets link a
i can see file with test area, shows in google sheets as view only but when I save to excel I cannot open and get message
format/extension not valid.
I'm really not sure what I am doing wrong or how to convert

 

[Peter_SSs]

@6StringJazzer
Two things:

1. Please note #4 (particularly the last two paragraphs) of the Forum Rules
   
   
2. Your post indicates no vba but the file/solution does use vba

 

[chef]

Apols as i was just following a response from a request for help and assumed a solution but unaware of forum breach,but unable to access anyway
If file does use vba then i will not be to use for reasons stated.

 

[6StringJazzer]

@6StringJazzer
Two things:

1. Please note #4 (particularly the last two paragraphs) of the Forum Rules
   
   
2. Your post indicates no vba but the file/solution does use vba

1. OK, I reread (haven't read the rules for a long time) and did not realize I was in violation. The rules are the rules but there are some solutions that are much easier to communicate with a sample file and I am baffled as to why this is against the rules, especially when the forum does not support file attachments. Nevertheless it was not my intention to skirt the rules.

2. I thought I had cleaned it up and see that I did not. My apologies. The CF solution in that file works only upon recalculation, so the VBA forces the recalculation.

Clearly the whole post should be disregarded. My edit window has passed, however.

 

[6StringJazzer]

P.S. It's not easy to find the rules.

 

[Peter_SSs]

I am baffled as to why this is against the rules

Hi Jeff
You would not be (& have not been) pulled up for providing a sample file. The problem for us is only doing that to answer a question and not giving, or at least outlining, the steps/code/formulas in the forum itself. Here is an acceptable example where a sample file has been provided but the relevant solution is also published directly in the forum. It is also a good example of one of the reasons (outlined in that forum rule) that we don't want a linked file as the only answer provided (try the file link in the post).

Nevertheless it was not my intention to skirt the rules.

I understand that and no punitive action was ever envisaged.

 

[6StringJazzer]

Thanks for the patient and instructive replies.

I would make the modest suggestion that a link to the rules could be added in the navigation such that you can get to the rules from the top menus of any page. Currently the only way to get there (that I know of) is to navigate back to the home page for the board (not the site) and go from there.

 

[6StringJazzer]

Here is a sample file that shows how to do this with conditional formatting, no VBA

Regrettably this does use VBA. It adds the conditional formatting rule

=AND(CELL("col")=CELL("col",A1),CELL("row")=CELL("row",A1))

However, this rule is only evaluated when the sheet is recalculated. If you merely change what cell you've selected, there is no calculation. So the file has a bit of VBA in the sheet module to force a recalculation every time the selection changes:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Me.Calculate
End Sub

I am not aware of a solution that requires no VBA at all.