Highlight column using conditional formatting

[canisdibellum]

little bit of an odd challenge, well it is for me anyways....can't seem to crack it....

Using conditional formatting I want to set up a spreadsheet so that an entire column will highlight if it contains a value I'm looking for...for example if the value I want is in A37 I want from A1 through A70 to turn green

OR

if a value shows up in a column, highlight the first cell in the column...for example if value is at A37 I want A1 to turn green

Cell A37 used in the 2 examples is arbitrary I want any cell in the column to activate the conditional formatting

yes I know it's odd and probably difficult to come up with a use, but is there a way to do it?

 

[Joe4]

Welcome to the Board!

Where is the "value you are looking for" stored?
That is, what exactly are you comparing A37 to (where is this "value you want" located)?

 

[canisdibellum]

a single absolute cell...whose value changes frequently

 

[canisdibellum]

And thank you for the welcome I've been lurking for years but finally had a question that I don't think anyone else has asked.

 

[canisdibellum]

a single absolute cell...whose value changes frequently

For purposes of example we'll say B2

 

[Joe4]

For purposes of example we'll say B2

Then, you can highlight the whole column, and then use this Conditional Formatting formula:
=$B$2=$A$37
and choose your highlighting color.

The key here is to use those absolute range references ($) to lock those ranges down so that every cell in that column looks at those exact cells.

 

[canisdibellum]

The A37 was just an example...i want it to trigger if any cell in range A1:A70 has the same value as B2

 

[Joe4]

Then use the Formula:

=COUNTIF($A$1:$A$70,$B$2)>0

 

[canisdibellum]

My friend you are a godsend...that is perfect, thank you!

 

[Joe4]

You are welcome!
Glad I was able to help.