# Auto Colour cells if a DATE is in adjacent cell.

1. ## Auto Colour cells if a DATE is in adjacent cell.

Hey guys,

I have a quick question that I'm sure someone could solve in a matter of seconds.

Columns and rows from A1:E107 full of product info. These are my stock lines.

All I'm looking to do is, if a dispatched date is entered into column F, next to the stock line, The line of stock will highlight in green.

That's all.

I think it's a conditional format of some type but I can't seem to get it to work.

Any Ideas?

2. ## Re: Auto Colour cells if a DATE is in adjacent cell.

Highlight all the data, i.e. A2:F107, assuming you have row 1 for headers. Then click on Conditional Formatting and choose Formula Is rather than Cell Value Is, and then enter this formula in the box:

=\$F2<>""

Then click the Format button, then the Patterns tab and choose green. Then OK your way out, and Excel will adjust the cell references as appropriate. Note, if you don't have a header row and highlight A1:F107, then the formula should be =\$F1<>"".

Hope this helps.

Pete

3. ## Re: Auto Colour cells if a DATE is in adjacent cell.

Maybe?

In Conditional Formatting rules.

=\$f1>0

Range A1:E107

4. ## Re: Auto Colour cells if a DATE is in adjacent cell.

Worked PERFECTLY!!!

I can't thank you enough!!!

Just one more thing though if you would be so kind!

column E is full of weights for my products... Is there any way I can have one cell say H3 keep a running total of the highlighted weights?

So If a row is highlighted, then cell H3 would increase by that amount of weight in column E?

5. ## Re: Auto Colour cells if a DATE is in adjacent cell.

Suggestion 1

In H1 and copy down

=IF(F1<>"",E1,"")

Or

In H1

=IF(F1<>"",E1,"")

In H2 and copy down.

=IF(F2<>"",E2+MAX(\$H\$1:H1),"")

6. ## Re: Auto Colour cells if a DATE is in adjacent cell.

I think you want one cell to contain the total of the weights on rows that are highlighted, so you can put this formula in H3:

=SUMIF(F2:F107,"<>",E2:E107)

Hope this helps.

Pete

7. ## Re: Auto Colour cells if a DATE is in adjacent cell.

Thanks Pete_UK,

My weights are in column F2:f107

In your forumula, how does E2:E107 fit into it?

8. ## Re: Auto Colour cells if a DATE is in adjacent cell.

Read through your previous posts. You have said that the dates are in column F and that the weights are in column E, so that is what my formula has assumed. If these have now changed then you need to adjust the formula to suit.

Hope this helps.

Pete

9. ## Re: Auto Colour cells if a DATE is in adjacent cell.

It would was also very helpful and kind to me (not wasting my time-trying to help you), if you had told us that i am in your IGNORE list....

10. ## Re: Auto Colour cells if a DATE is in adjacent cell.

@Fotis1991

I thanked you for your first contribution which helped me.

Your second, I was trying and tweaking. Then the other one came along which worked. It wasn't that your advice was ignored, I was simply in the process of testing it when I replied to Pete.

If I seemed rude, I do apologize! Your input was appretiated!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1