[Q] How can I color code bar in bar graph according to text in adjacent column?

[grimloktt]

Hello. Is there a way to color code bars in a bar graph dependent on the text in adjacent column of the data? I'm attaching a spreadsheet with a small example. I'd like the bars to be different color if they belong to vendor A or B and for it to be automated versus manually changes the bars' colors. Thoughts?

[Greg M]

Hi there,

Take a look at the attached workbook and see if it does what you need. It uses the following code:

Please Login or Register  to view this content.

You can change the highlighted values to suit your own worksheet requirements.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[grimloktt]

Thank you. This is exactly what I was after; however, I have no idea how to edit macros. I figured out how to add "Developer" tab in Excel but am not sure how to change the red text you noted above besides manually. Is there a simply run down you could provide or a link to a how-to?

Thnx!

[Greg M]

Hi again,

Many thanks for your feedback.

Great! At least we're moving in the right direction

When I talked about changing values, the "manual" method was exactly what I had in mind!

If you use the Alt+F11 keys it will take you into the Visual Basic Editor where you can change whatever values are appropriate to your new worksheet layout. I'm not sure exactly what you have in mind, but please feel free to ask anything you think I may be able to help you with.

Best regards,

Greg M

[grimloktt]

I'm pretty good w/ Excel--can even use Solver; however, I know nothing about VBA. I think my idea of manual and yours are different. I'm under the assumption when you are coding you can select cells versus manually typing everything in. Is that correct or did you manually type everything in?

Also, why is some text blue while others is black?

I feel completely lost...maybe I need to youtube some videos?

Thoughts?

[Greg M]

Hi again,

I typed all of the code "one letter at a time"

If you're running the Macro Recorder you can select cells manually and their references will be recorded ("typed") in the macro.

The colours used to display the different elements of the VBA Code are set by the Options >> Editor Format DialogBox of the VBA Editor.

As far as learning VBA is concerned, I started with extremely small "projects" - enter today's date in Cell A5 - print worksheets 1, 2, 5 and 8 from just one button click - etc. etc. It's much less overpowering than starting on Page 1 of VBA for Dummies and working your way through it. My approach worked for me, but quite possibly not for everyone.

Feel free to ask if you need any further information.

Best regards,

Greg M

[grimloktt]

Greg,

Hello. I would really like to use your script but feel like this is over my head. For my use, I would be making new worksheets for different locations and sometimes more vendors than two. So...some of my initial questions are:
- You set As String = "B12" Do I just input the top cell where the range starts?
- As String = "Vendor A" Same as above. Do I just put the text of the top cell in the range?
- Color range (255,0,0) and (0,255,0). What do I do here?

Frankly, while I'm really rather good at Excel, I don't know the first thing about scripts. My objective is the ability to apply a coloring script to different worksheets within the workbook. I feel a bit lost and like it's too much to ask of you...

[Greg M]

Hi again,

Never any harm in asking! :-)

I'm away from home/Excel until the middle of next week - I'll take another look at this when I get home.

Regards,

Greg M

[grimloktt]

Thank you!