Color fill cells in a sum range

[rwdozier]

This may a simple issue but I can't seem to locate a solution.

Example: Using the basic SUM(A1:A10) in cell A11, is there a way to highlight the cells A1 through A11. When the SUM() function is removed from cell A11, highlighting goes away.

I know Crtl-[ does this, but I would like it to highlight the cells in the sum() range automatically.

I have tried some macros and UDFs but I thought this should be much easier to accomplish.

Suggestions appreciated.

...Bob

[sandy666]

Conditional formatting:
Formula: =$A$11<>"" (or: =SUM($A$1:$A$10)=$A$11)
Range: $A$1:$A$11
Highlight: up to you

if I got it well

[Palmetto]

Hello Bob,

Welcome to the Excel Help Forum.
It sounds like you want cells A1:A10 to be highlighted if cell A11 sums to a certain value or is within a range of values. Is this correct?
Assuming so:

First, let's clear out CF rules, if any, to start fresh.
1. Select cells A1:A10, then from Ribbon Menu > Home > Conditional Formatting > Clear Rules > Selected Cells
2. With A1:A10 still selected: navigate to Conditional Formatting > New Rule
3. Choose the option to Use a Formula to choose which cells to format
4. Enter the formula you need. For example: = $A$11<>"" (example by Sandy666) or =$A$11>x (where "x" is a number you determine)
5. The key to the formula is it must evaluate to TRUE or FALSE.
6. Apply the desired formatting via the tabs in the dialogue box. Then OK click till done.

Here are a couple of links to info on Conditional Formatting.
Excel-Easy: Conditional Formatting
TechRepublic: Ten Cool Ways to Use Conditional Formatting

[Angela3]

Hi guys. Thank you so much for informative posts.