Excel 2010 - Conditional Formatting - Update rows dynamically

0
votes

Is there anyway to highlight a range of cells based on a cell value that's on another sheet in the same workbook?

For instance, cell B2 in Sheet1 contains a decimal value (in this case, it is 2). And the expected result is, highlight the range A2:B3 in Sheet 2.

But the cell B2 in Sheet1 is dynamic. Hence if i enter 7, then the range A2:B8 in sheet 2 must be highlighted.

Is this achievable through conditional formatting? I couldn't find a suitable function that could do this type of formatting. Probably it is possible with VBA but I don't know.

1
excelexcel-formulaconditional-formatting
It is achievable with conditional formatting =Sheet1!$B$2 is the cell you refer to from you condition and you define the conditions in the cells you want to highlight under which they need to be highlighted. It's quite wide question.. – Han Soalone
Thanks for your response @SickDimension Sorry I thought I could explain with screenshots but I couldn't as I didn't have privilege to upload them. From your comment, I understand that the cells can be highlighted through defined conditions as long as they have some values (empty cells or rows or columns can't be highlighted by Conditional Formatting), am I right? – joso
empty cells or rows or columns can't be highlighted by Conditional Formatting - In other words, irrespective of the value of a cell or a row, can "conditional formatting" still format, based on the value of the cell B2 in Sheet1? – joso
That is incorrect, being empty cell has nothing to do with if conditional formatting can be applied. Any cell can have conditional formatting that can be triggered via reference cell for ex. =Sheet1!$B$2. Now the biggest question is under what conditions does it need to be triggered? Think conditional formatting that uses a formula to define if the cell needs to be highlighted. It's impossible to list the possibilities, but you should read this to get started - Link – Han Soalone

1 Answers

0
votes

Yes.

Clear CF from ColumnsA:B in Sheet2, select them and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=AND(ROW()>1,ROW()<Sheet1!$B$2+2)

Format..., select highlighting of choice, OK, OK.