SEARCH

The How-To Geek Forums Have Migrated to Discourse

How-To Geek Forums / Microsoft Office

(Solved) - Automatically making Bulk of Charts for the large excel sheet &then Customizing

(27 posts)
  • Started 1 year ago by amrinderminhas
  • Latest reply from amrinderminhas
  • Topic Viewed 2797 times

warlock
warlock
Posts: 4100

You are amazing.

Posted 1 year ago
Top
 
amrinderminhas
Posts: 59

@moreeg
Perfect!!!!! the excel file u uploaded is really the best solution more than thousand words.
I have read the programming done and everything else. Though i dont understand those languages but it just give the idea of how to manipulate things.
I have added another cell and then going by same rules i am able to add that cell values in chart (its something fascinating for an amateur like me)

Moreover again understanding a little language i am able to give shade to that added column.
' The only little more help that i needed is that if i would like to have shade of my choice other than the dark shade then what should be done? (though i think it ll be Interior.TintAndShade = -0.14999847407452 to be changed, lol)....isnt it? '

@sparks @Enthusiast
I am really fan of urs as u always try to help me as much as u could.
The three of u make me so confident that i could now take up any challenge of solving the so called difficult problems.....

Posted 1 year ago
Top
 
Enthusiast
Enthusiast
Posts: 566

Good to hear amrinderminhas and as always, glad to be of service.

@moreeg, as is usually the case, our government entity has seen fit to exclude screencast from our available sites. Minor imposition for me, since I can follow what you are doing, however, if I am not at home, I cannot upload solutions either. As you said, this will be a useful addition to the tools available for helping those in need.

Posted 1 year ago
Top
 
amrinderminhas
Posts: 59

actually i just want one more answer which i have asked previous to this post that how could i give the shade of my own choice to the row i select....??
The shade that moreeg has given is dark brey in colour.....

Can any of u give me the solution before i close this knowledgeable topic.........

Posted 1 year ago
Top
 
Enthusiast
Enthusiast
Posts: 566

amrinderminhas,

Change the following line in the macro for the color you desire:

Change this:

Range(Target, Target.Offset(0, 13)).Interior.TintAndShade = -0.149998474074526

To one of these:

Range(Target, Target.Offset(0, 13)).Interior.Color = 65535 'Yellow Highlight

Range(Target, Target.Offset(0, 13)).Interior.Color = 255 'Red Highlight

Range(Target, Target.Offset(0, 13)).Interior.Color = 15773696 'Blue Highlight

Range(Target, Target.Offset(0, 13)).Interior.Color = 5287936 'Green Highlight

If these colors are insufficient, let us know and we will work with you to get whaqt you want.

Posted 1 year ago
Top
 
moreeg
moreeg
Posts: 842

Hi Amrinder

Here are 2 other ways to select your own highlighting colours .....

Take a look at this web page for a complete set of colours and their codes. So if you decide to choose the yellow, for example, in the chart it is identified as [color 6]. You would then change the VBA code to....

Range("A2", Range("A1").Offset(LastRow, 13)).Interior.ColorIndex = 0
Range(Target, Target.Offset(0, 13)).Interior.ColorIndex = 6

Another way is to let Excel generate the code you need based on the shading you choose. You would do this with the "Macro Recorder".

Select a cell in an unused part of your worksheet or a blank worksheet and then go to View/Macros and click on the little arrow under Macros and select "Record Macro"
A pop-up will appear telling you the Macro name it is assigning (Macro1 if it is the first one 2 if second and so forth). Make a note of the macro name and click on OKAY
Change the highlight of a cell. If you are not happy with that go to the next cell and try another highlight. When you are done go back to View/Macros and click on the little arrow under Macros and select "Stop Recording"
Finally click on the little arrow under Macros again and select "View Macros". Highlight the macro you just created (remember I topls you to make a note of the assigned macro name?) and then click "Edit" on the right.

You will see something like this

Sub Macro7()
'
' Macro7 Macro
'

'
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("P21").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("P22").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveWindow.SmallScroll Down:=4
Range("P23").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveWindow.SmallScroll Down:=-2
End Sub

From here it is just a bit of work to take the code generated for the colour you want and transpose that to the main macro. You'll need to be a bit creative or just come back and ask us.

However, in general, this is a great way to begin constructing your own VBA code. Start the recorder, do the things you want to automate, stop the recorder and then you'll have a good basis for what you want.

Accepted Answer · Posted 1 year ago
Top
 
amrinderminhas
Posts: 59

thanx for the help and tutorial to solve this problem.
I really admire ur social work doing to the community. We have a lot of ideas and thoughts to make work smooth and quick but do not have the skills to make it real.... There comes the real heroes like u.......thanx to all once again......
C u Again...
Take care
God Bless You...

Posted 1 year ago
Top
 



Topic Closed

This topic has been closed to new replies.

Enter Your Email Here to Get Access for Free:

Go check your email!