MACRO OR CONDITIONAL FORMATTING?

[CarolineH12]

hi everyone, I have no experience really of macros (or how to create them) or conditional formatting so I'd be grateful for some pointers - not even sure what I need really!

I have a spreadsheet which has the customer 4-digit code in column A and depending on their payment terms I need them to be coloured one of 3 colours (orange, blue and pink depending on whether they pay at 30 days, 60 days or 90 days). This is a monthly task and can be time consuming to pick every one out and colour them individually.

How would I do this, I'm a complete dummy!

Thanks!

[#2]

@CarolineH12 

How and where is the payment term specified?

Perhaps you could attach a small anonymized sample workbook.

[#3]

@Hans Vogelaar 

it's a very simple sheet - see attached

[#4]

@CarolineH12 

Thank you. See the attached version - now a .xlsm workbook, so you will have to allow macros when you open it. Click the button to colour the cells in column A.

[#5]

wow that's great, how have you done that!? It would help me to know because I've only given you part of the list.

[#6 · Accepted solution]

@CarolineH12 

Press Alt+F11 to activate the Visual Basic Editor.

You can see the code of the macro in Module1.

Clicking the button calls this macro.

It will still work if column A and/or column B have more data.

[#7]

Thank you so much for your help.

[#8]

@CarolineH12 Okay, you seem to insist on a VBA solution, but you really don't need it. I wonder why you would colour code every customer first, rather than just give them a (number) code and then use Conditional Formatting to look up the code and to colour them. But that's your choice, of course. Excel is great with numbers but requires more complicated solutions if you want to work with colours. Attached an example that uses conditional formatting. Much easier, I believe, and no need for coding at all, and you can expand the ranges to suit your needs.