Conditional formatting VBA help

**neildexter** · 04-03-2014, 05:32 AM

Hi,

I am creating an online order spreadsheet and would like to use conditional formatting, depending on the progress of the order.

I have successfully conditional formatted a log to change colour if it has not been received and if it has been dispatched. I want to conditional format it to a green colour if the item has been received.

If you take a look at the attachments, you can see I have circled two cells which determine the conditional format. Now I cannot get conditional format to work for it to turn green because the dispatched cell dives it the amber colour. It is when the received cell has a Y in it that I would like it to turn green.

Any ideas on how I can achieve this?

Thank you in advance,
Neil.

**Winon** · 04-03-2014, 06:32 AM

Hello neildexter,

Welcome to the Forum.

It would depend on which Conditional Format has preference over another, and we would only be able to tell from a sample Workbook, and not pictures.

Kindly, Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Regards

**neildexter** · 04-03-2014, 09:45 AM

Thank you for your reply. I have attached a sample workbook so you can see it in action.

**Winon** · 04-03-2014, 10:41 AM

Hello neildexter,

Thank you.

Excel file extensions with .xls do not take kindly to Conditional Formatting with overlapping Cells, and that is why you have a problem. Saving it in an .xlsm format solves this problem as illustrated in the attached Workbook.

Regards

**neildexter** · 04-03-2014, 01:13 PM

I would really like to use the current .xlsm format, but I have macros in my spreadsheet and Excel refuses to save it in this format. Do you know of any way to enable macros, or their equivalent in the new file format?

The macros I use are selecting a month from the front screen, as I have all the months listed for the year. I also have a macro to clear the entire spreadsheet of inputted data.

**Winon** · 04-03-2014, 01:55 PM

Hi neildexter,

I would really like to use the current .xlsm format, but I have macros in my spreadsheet and Excel refuses to save it in this format.

I am really confused here. Your profile shows that you are running Excel 2011, and yet you state the above.

The "Macros" you are running should bear no relevance to your issue in this instance.

Are you perhaps running the Workbook in compatibility mode? If so, it will not work.

Please confirm which version of Excel you are trying this on.

Regards.

**neildexter** · 04-03-2014, 04:26 PM

When I try and save it in the new format I get the error shown in the attachment.

I am using Excel Mac 2011.

**Winon** · 04-04-2014, 02:32 AM

Hello neildexter,

I know there are certain compatibility issues between Mac and PC o/s's, and this could well be one of them.

Unfortunately I do not have access to a Mac for testing, and I would not be able to assist you further. However, one thing I did notice was in your Conditional Formatting Formulae, you had the Formula as; =Apr!$L$11="Y". It seems to work on the Mac, whilst my system won't accept it other as; =$L$11="Y".

What if maybe you change all my CF formulae to include =Apr!$L$11="Y",etc.?

Hope that helps!

**neildexter** · 04-04-2014, 04:40 AM

I cannot see where you found that the conditional formulae is =Apr!$L$11="Y", when I go into conditional formatting box, all I see for the formulae is =$L$11="Y"

I don't understand how that would work when you say to include =Apr!$L$11="Y" in all of CF. If you entered an order in row 14, it would still be formatting depending on the order status in row 11.

**Winon** · 04-04-2014, 06:14 AM

Hello neildexter,

I cannot see where you found that the conditional formulae is =Apr!$L$11="Y"

That shows in the sample Workbook you have posted in Post#3

I don't understand how that would work when you say to include =Apr!$L$11="Y" in all of CF.

I did not say that it would work.

What if maybe you change all my CF formulae to include =Apr!$L$11="Y",etc.?

I have merely made a suggestion based on my observation of your CF formulae in the Workbook mentioned in the second line above. It seems that our different Plantforms are not interpreting formulae the same way, hence the problem.

I am almost certain that this is a Mac related issue, since it works like a charm on my system.

Regards

**Winon** · 04-04-2014, 06:41 AM

Hi neildexter,

O.k. I have taken your Workbook from Post#3, made some changes, and saved it, regardless of a warning about significant loss of functionality with regards to the CF.

It seems to work on my side. Mind giving it a go on your side as well?

Thank you.

**neildexter** · 04-04-2014, 01:17 PM

Thank you so much Winon, works how I want it to. You are a life line!!

**Winon** · 04-04-2014, 04:44 PM

Hi neildexter,

You are welcome.

Glad I could help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

You may also Click on the Star to the far left, at the bottom of this Post, to Add Reputation.

Thanks.

**neildexter** · 04-05-2014, 07:25 AM

Is there a simple way to copy the CF from the sample workbook into my workbook and copy the CF down rows below the initial?

**Winon** · 04-05-2014, 07:51 AM

Hello neildexter,

Yes, if the layout in your Workbook is the same as the sample Workbook. Simply Copy and PasteSpecial>Formats. Otherwise, just send me a sample layout of your Workbook, and I will gladly help you doing it.

Regards.

**neildexter** · 04-08-2014, 06:17 AM

Thanks again Winon. I have now successfully formatted my spreadsheet.

**Winon** · 04-08-2014, 10:50 AM

Hello neildexter,

Thank you for the update, that is great news!

Regards.

**neildexter** · 04-26-2014, 12:50 PM

Hi,

Sorry to bother you again with this issue. I have been filling in order details, which all worked fine until I got to row 31. When I enter either a 'y' or 'n' in field L31, it conditional formats all the fields where you enter order info. I have tried to work out what is causing this, but to no avail. Would someone be able to have a look for me to see what the problem is? Thank you.

I have uploaded a spreadsheet showing the problem.

**Winon** · 04-26-2014, 01:23 PM

Hello neildexter,

Hard to say what went wrong, but what I have noticed is that the Conditional Formulae again appeared as =Jan!$K11="N" instead of just $K11="N".

Please try the attached WorkBook now.

Regards

**neildexter** · 04-27-2014, 10:26 AM

Works a treat now, thank you so very much. :D

**Winon** · 04-27-2014, 10:35 AM

Hi neildexter,

Thank you for the feedback.

You are welcome.

Glad I could help.

If that takes care of your latest issue, you may also Click on the Star to the far left, at the bottom of this Post, to Add Reputation.

Best Regards.

**neildexter** · 04-27-2014, 01:24 PM

How do you find the Conditional Formulae? Which cell do you have active to see what you are seeing, so I know for future reference. Thank you.

**Winon** · 04-27-2014, 01:36 PM

Hello neildexter,

In this particular situation, with your setup as it is, all you have to do, is to click on Cell B11, and then select Conditional Formatting>Edit Rules, then check the formulae applied, and everything will become clear to you, with that sharp brain of yours.

Please feel free to ask for more clarification, should you become stuck, which I doubt!

Kind Regards.

**neildexter** · 04-27-2014, 03:52 PM

Thank you for explaining it to me.

**Winon** · 04-28-2014, 05:01 AM

Hi neildexter,

You are welcome!

Regards.

**neildexter** · 05-04-2014, 01:25 PM

I want to conditionally format cells that have ANY value in. As you will see on the attached spreadsheet, I have a column for items that have been returned to sender. If there is ANY value in these cells, I want the cell to turn amber. Excel will not let me conditionally format cells if I do not put a value in for conditionally formatting it.

If these cells have ANY number in, I want it conditionally formatted, if there is no value in it, it can stay as is.

Is this possible?

**Winon** · 05-04-2014, 02:06 PM

Hi neildexter,

Perhaps as per the attached sample Workbook?

Regards.

**neildexter** · 05-04-2014, 02:12 PM

Can I use that CF just to CF the column returned? I have replicated what you have done, but only on the cells within the returned column, but could not get it to work.

**Winon** · 05-04-2014, 02:21 PM

Hello neildexter,

Thank you for the feedback.

Would the following attached Workbook then suffice?

Regards.

**neildexter** · 05-04-2014, 02:24 PM

That is perfect!! Thank you so much. What formula did you put in so I can replicate this through the column?

You are a legend Winon!!

**Winon** · 05-04-2014, 02:38 PM

Hi neildexter,

It is actually very easy. Click on the relevant Cell in column M where you want to apply the Conditional Formatting, then click on manage rules>Enter formula =$M23<>"", Select desired format, and O.K. way back to "Applies to", and enter the range you want it applied to. Click "Apply", and you are set to go.

Hope that helps.

Regards.

**neildexter** · 05-04-2014, 02:51 PM

Thank you again!! Managed to get it working, well to some degree. When the cell value is 0, it still CF's it and I don't want it to. Can this be done or will I have to have it CF'ed with 0 in the cell? See image I have attached as to what I get.

**Winon** · 05-04-2014, 03:05 PM

Hello neildexter,

Use =$M23<>0 as a CF formula.

Regards.

**neildexter** · 05-04-2014, 03:21 PM

Is there anything you don't know? Did you make Excel for MS, because it sure looks it with your knowledge.

It's all working how I want it to now. Thanks again!!

**Winon** · 05-04-2014, 03:38 PM

Hi neildexter,

Thank you for your feedback, and kind words.

Glad I could help.

If you are satisfied with the solution, then you may also Click on the Star to the far left, at the bottom of the Post, to Add to My Reputation.

Regards.

Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Re: Conditional formatting VBA help

Thread Information

Users Browsing this Thread

Similar Threads

Bookmarks

Bookmarks

Posting Permissions