[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 108
  • Last Modified:

applying shading to rows using VBA

Dear Experts:

I got a list with thousands of rows with the following make-up. For example

Column A         Column B
90-234-58-75   Product A
Quantity            38
Marking            100    
90-234-58-59   Product B
Quantity            90
Marking            95    
etc.

I would like to apply shading to these  rows in an orderly/uniform manner using a VBA macro so that ...
... rows with the product numbers get a red shading ...
... the rows featuring the quantity get a grey shading
... the rows featuring the marking  get a dark grey shading

I have attached a sample file with details for your convenience.

Thank you very much for your great help.

Regards, Andreas

Row-Formatting-Macro.xlsx
0
Andreas Hermle
Asked:
Andreas Hermle
  • 5
  • 4
1 Solution
 
SteveCommented:
This can be done using conditional formatting.
Are you sure you only want a VBA solution?
0
 
Andreas HermleTeam leaderAuthor Commented:
Conditional Formatting, sounds interesting? But how is this done?
Thank you, Regards, Andreas
0
 
Andreas HermleTeam leaderAuthor Commented:
There is a slight error in my sample file
The 'Quantity' and 'Marking' entries are not mentioned in the sample table only in the shaded version of this table.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
SteveCommented:
The attached file has Conditional Formatting > Based upon Formula.

This formatting is then set across A:B

Does this seem to work for you?
C--Users-shall-Desktop-Row-Formatting-Ma
0
 
Andreas HermleTeam leaderAuthor Commented:
Hi Steve,

thank you very much for your swift help. Unzipping your file results in strange file types.
But I managed to open the xml file and I could see the formulas behind the condtional formatting. I will try them out and let you know. This will take a while since I need to look after some other stuff right now. Thank you very much. Regards, andreas
0
 
SteveCommented:
It shouldn't need unzipping....

It has probably just lost the .xlsx extension.
The expert exchange file name convention is a tad longer than it needs to be.
(an xlsx file is just a zipped folder in disguise)
C--Users-shall-Desktop-Example.xlsx
0
 
Andreas HermleTeam leaderAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for AndreasHermle's comment #a40211452

for the following reason:

Hi Steve,

great, works like a charm, thank you very much for your great help. Regards, Andreas
0
 
SteveCommented:
Andreas, it seems you have accepted your own solution and closed the question.

I guess in error after the comment that it works like a charm.

I have opened the question back up for you to hopefully allocate points to my solution.

Thanks,
Steve,
0
 
Andreas HermleTeam leaderAuthor Commented:
Oooops, Steve, I am really sorry, my fault of course.

Also, Thank you for bringing this zip/xlsx issue to my attention.

Again, thank you very much for your great and professional help.

Regards, Andreas
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now