IF function Circular reference

[anstar]

and my head is spinning.
I'm trying to do something so simple and I can't seem to figure it out, which doesn't bode well for my chances of completing the project I've set for myself.

I need to set up a function that will change the font to red if the number goes above a certain point. I understand I need "if" but I try it, I get a circular warning pop up. It can't be that difficult. I managed to get it to add all the cells I wanted together and come up with a total. Why can't I get it to change color if it goes too high? <sigh>
Excel is going to drive me insane!

[glennchung]

It seems like you want cell color to change on the cell with the total? You should use Conditional Formatting on the cell with the total. Highlight the cell with total, go to the Home ribbon > Conditional Formatting > Highlight Cells Rules > Greater Than > then put in a threshold number. The cell with total will change color according to your selected format when it goes over the threshold value.

[hoyasaxa215]

Can you just use conditional formatting? Conditional formatting > Highlight cell rules > Greater than > enter whatever number you want in the first box and then in the 2nd box click the dropdown and select "Custom Format..." and just change the font to red.

[ben_hensel]

Can you post your workbook?

I can't really imagine how you could get a circular reference out of conditional formatting, so I can't propose a solution without seeing that.

[anstar]

No, sorry I wasn't clear. I want the font color to change. I know how to make the cell color change.

[anstar]

I tried this too. I'm doing something wrong.

[hoyasaxa215]

If you go into the custom formatting option, you can set it up so all it does is change the font color red without changing the cell color.

[anstar]

how do i post my worksheet

[hoyasaxa215]

Click on "Go Advanced" at the bottom of the message box and on the next screen click on the paper clip icon at the top. Then select the file you want to upload.

[FDibbins]

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =A1>10 (or whichever cell/value you need to use)
Then select Format/Font, select the co0lor you want

[anstar]

e4 for example.
if the number 54 or more, I want it to turn red

How do you delete a post?!?!?

[anstar]

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =A1>10 (or whichever cell/value you need to use)
Then select Format/Font, select the co0lor you want

I'm doing something wrong, because I've tried that several times and it's not working.

[FDibbins]

Not sure why you are using this for the CF rule in E4...
=MOD( ROW( ), 2) =1

All you need there is...
=E4>53

And for the Applies To range, you can just use this...
=$E$4:$W$32

[anstar]

That CF rule just shaded every other row so it was easier to read

All you need there is...
=E4>53

And for the Applies To range, you can just use this...
=$E$4:$W$32

Sorry, I don't understand where that goes, or how it turns my font red.

[FDibbins]

OK understood

Then just add my suggestion as a new rule (and leave your rule there)

On a side note, be aware that, if used in excess, CF can start to slow your file down

[anstar]

I'm (obviously) not great with excel. Don't I have to highlight each cell I want to use CF for? Is there a shortcut like the click and drag for formulas? I need entire columns to change color if necessary.

[ben_hensel]

Yeah, just change the range defintion in the rules section.

...I'm also not a big fan of the way you have your data organized; splitting up the data with subtotals in the middle is confusing to me.

Does the attached work for you? (I just filled the table with nonsense using RANDBETWEEN). It means you can also apply one CF rule to the whole data range; much easier, IMO.

[anstar]

I'm also not a big fan of the way you have your data organized; splitting up the data with subtotals in the middle is confusing to me.

Sorry. I have to divide it that way. We work in quarters. I need the breakdown in quarters. The subtotals by quarter are more important than the individual monthly totals actually.