[RESOLVED] Excel Differences

[gilly]

Hello

I'm struggling to write an excel macro or formula to work out differences in columns and would really appreciate if some kind person could help me out:-

See spreadsheet attached.

I need to do the following calculation.

Work out the figure for Column H minus Column E then if this figure is less than Column A i need to highlight the row in Yellow.

Is this possible?

[Hack]

Moved From FAQ Section

[anhn]

No need to use macros, use conditional formating with formula:
=($H4-$E4)<$B4

[gilly]

Thanks very much.
How do i ignore the rows which are blank? Currently when i copy down your forumla i get the word "TRUE" in all the empty rows.

Also, is there any way to colour the row in yellow if the result is TRUE?

thanks so much

[anhn]

No way to use cell formulas to change the color.
As I said, use Conditional Formating

[gilly]

Thanks, reading about conditional formatting now. Is it possible to ignore the rows that have blanks in the Stock column & the Items in Stage column?

[gilly]

i have tried adding to your formula below. But my syntax is wrong - can you help me fix it?

=AND($H4-$E4)NOT(ISBLANK)<$B4

I'm trying to ignore the rows which have blank cells in columns H or E

[anhn]

=and($h4>0, $e4>0, $h4-$e4<$b4)

[gilly]

Thanks Anhn, unfortunately i tried your formula however it returns FALSE in the rows that have blanks....i need it to output no word (True or False) in those rows with blanks.

Sorry to be a pain. I really appreciate your time.

[Siddharth Rout]

Thanks Anhn, unfortunately i tried your formula however it returns FALSE in the rows that have blanks....i need it to output no word (True or False) in those rows with blanks.

Sorry to be a pain. I really appreciate your time.

Follow these steps. i am using your workbook as a reference...

Click on cell E4
Click on tools format=>Conditional formating
Select "Formula is" in Condition 1
type

=AND($H4-$E4<$B4,LEN($E4)>0)

in the textbox next to it. after that click on the format button. Under the patterns tab, select Yellow. and click ok.

Now click the format painter button from the tools, and paint the rest of the cells down. The cells will automatically be conditionally formated.

Hope this helps...

If I misunderstood your query, then do explain it to me again and I will try and help...

Edit: I am attaching a copy of your workbook where the cells are conditionaly formated...

[gilly]

Thanks for your time. However the cells which are blank in columns H and Column E should output nothing i.e leave the cell White.

Instead the conditional formula outputs the word FALSE if the cell is blank.

How do i get it to output nothing?? i.e. leave the cell White / Empty

[anhn]

Thanks for your time. However the cells which are blank in columns H and Column E should output nothing i.e leave the cell White.

Instead the conditional formula outputs the word FALSE if the cell is blank.

How do i get it to output nothing?? i.e. leave the cell White / Empty

What? Conditional formula must return either TRUE or FALSE. If TRUE then set background to Yellow, otherwise leave it as default.
You won't see any value TRUE/FALSE from Conditional formula.

[gilly]

Sorry. I understand now. . I was copying the cells instead of filling the cells with the formula.

thanks for all your help

[Siddharth Rout]

I was copying the cells instead of filling the cells with the formula.

thanks for all your help

If you would have gone through what I mentioned above, you would have seen that you could have used the format painter to do the job. you don't need to put the formula manually

[anhn]

No need to know how to use format painter.
Highlight Data range A4:H25 starting from A4, enter Conditional Formula just once as:
=AND($H4>0, $E4>0, $H4-$E4<$B4)
for cell A4. All other cells will have Conditional Formulas updated with their row numbers automatically.

[Siddharth Rout]

No need to know how to use format painter.
Highlight Data range A4:H25 starting from A4, enter Conditional Formula just once as:
=AND($H4>0, $E4>0, $H4-$E4<$B4)
for cell A4. All other cells will have Conditional Formulas updated with their row numbers automatically.

Yup that's another way to do it...

The third way to do it would be to copy cell A4 after the conditional formating is done AND then do a "pastespecial-Formats" on the rest of the cells.

[gilly]

Work brilliantly, thanks to you both.

xxxxxxxx