Conditional Formatting 1

nelson97 · May 22, 2005

How can I use conditional formatting to achieve the following:

I have a cell on Sheet 3 Range D5 with a predefined max of 45.
Its value increase and decrease accordingly based on calculation on other events. I would like 3 cells on the first sheet to show a breakdown of the value. cell 1 will show the value if it's between 0-15, then cell 2 if between 16-30, and cell 3 if between 31-45. For example if D5 is 20,
cell 1 = 15, cell 2=5, cell 3=0

Thanks!

byundt · May 22, 2005

I suspect that you want formulas rather than VBA code or Conditional Formatting.
=MIN(Sheet3!D5,15) for the first cell
=MIN(MAX(Sheet3!D5-15,0),15) for the second cell
=MAX(Sheet3!D5-30,0) for the third cell

If you really meant Conditional Formatting (which can make the cell receive different highlighting or font color based on the value of another cell), the trick is to use named ranges when referring to cells on a different worksheet. Use the Insert...Name...Define menu item to create a name for Sheet3 cell D5. Let's say you name it TestCell. You may then use it in a "Formula is" condition like:
=TestCell<=15 for the first cell
=AND(TestCell>=16,TestCell<=30) for the second cell
=TestCell>=31 for the third cell

Brad

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Conditional Formatting 1

nelson97

IS-IT--Management

byundt

Technical User

Similar threads

Part and Inventory Search

Sponsor