auto/coditional format

[willemeulen]

I have the following sheet:

Column A = value inserted by user
Column B = value inserted by user
Column C uses index function using column A + B to return a formula
The next 5 columns (D-H) will have additional values, when filling out
the form these cells will be empty and will be inserted by the user.

My goal is that excel automatically gives the cell a
shade/color/marking etc when a value needs to be added.

Example:

Column C

=(D12+E12)/3 * F12 --> I want Excel to shade cells D12,E12 and
F12

=(D13*3.14)/G13 *H13 --> I want Excel to shade cells D13,G13 and
H13

Now it is easy to see which cells require a value to be inserted by the
user or when a value is missing.

Is this possible with conditional formatting using a formula? Or do I
need to use VBA (don’t have any knowledge of VBA). It should be
possible; I mean the trace precedents tool shows you all cells used in a
formula/cell.

Thanks,

Willem

 

[Tom Hutchins]

Here is one way...

Select D1, then select all of column D (so D1 is the active cell within
column D). From the Format menu, slect Conditional Formatting. Change 'Cell
Value Is' to 'Formula Is'. In the formula textbox enter this formula:

=AND(CELL("type",$C1)="v",LEN(D1)=0)

Click the Format button, select the formatting you want to highlight cells
missing values, and OK your way back out of the conditional formatting
dialog. Use the Format Painter to copy the formatting from column D to F
though H (or apply the same conditional formatting to each column
individually. Just change D1 to E1, F1, etc.)

This will highlight empty cells in columns D - H if column C in that row has
a non-text value or formula in it.

Hope this helps,

Hutch