I have a spreadsheet that has the following columns: Tactic, Impressions, Engagement, Clicks and Forms. The tactic column contains a dropdown menu on each cell that has x amount values, for simplicity lets say it has 2, Value1= "A" and value2= "B". if cell A1(where the Tactic column is) contains the value "A" then I want to highlight in say "Yellow" colour the adjacent cells of the Impressions and Engagement columns and if the value of cell A1 is "B" I want to highlight the adjacent cell of teh Forms colum in yellow but not any other column. Basically, I need to be able to select a Tactic and the columns that require data to be entered based on that tactic to be highlighted to the user. And this needs to be applied to x number of rows in the spreadsheet?

I can accomplish this partially by using conditional formatting and using a custom formula. please see picture below but the problem is that the rule only applies to that particular range, in this case E2-F2. I need excel to "Know" that when the value of the Tactic column in any row changes or it is filled up for the adjacent cells to be highlighted based on the formula. Is there a way to make this conditional formatting dynamically obtain the row index where the Tactic selection was made and apply the cell colour to columns E and F but only on the row where the selection was made without having to hardcode each row with this conditional formatting?

Thanks for your help!

CodePudding user response：

On "Formula" use =$D2="A"

On "Applies to" select the whole Column-Area

This should do the trick

CodePudding user response：

You want one set of cells across the row highlighted on condition A and a different set of rows highlighted for condition B.

You will solve this by creating TWO rules.

• Rule "A" is the =$D2="A" rule, with an applies to range that is the rows you want to highlight for condition "A".
• Then click "New Rule" and add =$D2="B" and make the applies to range only the column you want to highlight on condition "B".
• You can just keep on going and adding a condition C, D, E, etc... one new rule in the rule manager for each cell highlight combination.

The rules are applied in order, so in some cases where a row may meet more than one condition, you'll need to move the rules to be in a specific order and check the Stop if True box. This creates an "If/elseif/elseif..." structure.

But in your use case, a row can only trigger one of A or B conditions, so there's no sequencing necessary.

Conditional formatting can be counter-intuitive. In Excel we have to change our thinking away from a procedural approach to a fully object-oriented approach. We don't "send" values to a cell, rather, we can only tell the cell, by the means of its formula, how to determine its own value. But in conditional formatting we have to go back to some procedural thinking in the way we develop the conditional logic.