Changing a rows color (conditional formatting) based on a texted value that correlates to a number

Occasional Visitor

Good evening,


I have a sheet with a row of data ending with a two letter state, eg. TX. I would like the row to change colors based on another sheet that has the state and a numerical value associated with it that changes every few hours manually. 


Example : sheet 1 has a few names, data, and a state (TX)

                Sheet 2 has TX in cell B2, with a value associated in B3 at 78 

                Because the value on sheet 2 is 78, the rows on sheet 1 needs to be orange 

                When the value exceeds 500, the row on sheet 1 needs to be red that has the associated                        (TX) in the state column. 


I've conditionally formatted my sheet 2 table to the desired metrics, but I need to be able to change sheet 2, and have it automatically change colors of certain rows for those states on sheet 1 as the numbers increase or decrease on sheet 2. 


Thanks for any assistance. 


1 Reply



Could you upload a sample of your file (or the actual file, so long as it contains no personal or confidential information)? It's a lot easier to work with the actual file than one's description (even though you've done a decent job describing it).


A question before you do: is any given state (TX being the example you use, so let's stay with that) appearing more than once on either sheet 1 or sheet 2? Said another way, are the values that might drive this formatting truly unique to each state, or are there multiple rows on sheet 1 that pertain to TX? Or multiple rows on sheet 2 that have different variables assigned to them?