I created a chart that was automatically assigned colors and shading that I like. I would like to use the same shading and color scheme for other charts (not the same chart type) however I cannot seem to figure out exactly what color and shade was automatically assigned (it is not one of the standard ones). Is there some way to copy the fill without copying the entire chart format?
View 2 RepliesHow do I in Excel 2003,2007,2010 shade in an enclosure I have drawn using scatterplot or some other line drawer with a grey darkness of my choice for that particular enclosure?
View 1 Replies View RelatedI'm working on a criteria matrix in Excel 2010 that automatically plots a single member in a Scatter Chart based on the two values. There are 4 suppliers listed in Column D starting in cell D4 thru D7. The "x" value is listed in Column E starting in cell E4 thru E7. The "y" value is listed in Column F starting in cell F4 thru F7.
The scatter chart will plot the points correctly, however, there are two issues: 1) If I try to insert a data label using the "Series Name," or in this case, the supplier's name, it will lists ALL of the suppliers Column D. It will not list the single supplier listed in cell D4. 2) The scatter chart appears with gridlines as a 4x4 matrix with a total of 16 cells. The "x" and "y" axis both start at 0 and go to 4. I can shade the entire chart one color. However, I want to shade some of the cells with darker and lighter shades.
I am creating a line chart with markers using 5 series of data. I want to colour in/shade the area under the lowest series.
View 6 Replies View RelatedI have a spread sheet with various tick boxes, that when ticked calculate an accumulative percentage in a cell. This cell is the basis of my graph. e.g. if the cell displays 80% - the chart with show 80% - simple.
however, I want to write a vba code that changes the fill colour of the chart depending on the percentage.
i.e. if the percentage data = 0-49% I wish the chart to display as red. 50-69% - yellow and 70%+ = green.
I'm trying to create a macro to add a fill effect on the bars of my chart.
However, I don't want it to change the bar's current color. Just to do the effect on the current color.
Code:
Sub fill()
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
[code].....
I have two columns. Date and Day
E.g.
Date Jan 1, 2007 and it falls on on a Monday in the Day column.
Date Apr 6, 2007 and it falls on on a Friday.
Date Dec 25, 2007 and it falls on on a Tuesday.
What I want is to Colour Code the DAY column in case it is a holiday falling on a Monday or a Friday.
I have a spreadsheet I use to keep my bank balance in...
I have this forum at the VERY top of it to show my bank balance:
=G3+SUMIF(H4:H2999,"X",E4:E2999)-SUMIF(H4:H2999,"X",F4:F2999)
Once an item clears, I put an X in column H, and then it updates the balance at the top to make it so I can reconcile easily.
What I want to do is have every line without an X in column H to show up a shaded color... any easy way to do this?
I'm using the below code to shade rows, but right now it's only coloring the first cell in B12. I'd like to shade the row from B to the last used column
[Code] .....
When I run a particular module of code that unhides several rows of a worksheet, revealing cells that are automatically populated and editable by the user, there are a few cells that are lightly highlighted in blue. This isn't to be misunderstood as background ... background shading goes righ to the 4 lines of the cell grid. This shading is a few pixels off the gridlines. Similarly, there are highlighted row numbers and column letters. The cells that are highlighted are a few of the cells that are locked for editting and are a result of a copied over formula. The highlighting goes away when you click on that cell.
One could almost say something is identifying the locked cells of the worksheet.
.Code:
Sub populate_existing()
Dim wshmain As Worksheet
Dim wshcore As Worksheet
Dim CRID As Long
Dim vlrange As Range
Set wshmain = Worksheets("Main")
Set wshcore = Worksheets("CONTROL_1")
CRID = wshmain.Range("B14")
Set vlrange = wshcore.Range("A:DZ")
[code]....
The cells highlighted are: G26, N26, X25, AE25, AE27, AL26, K42, R42, Y42, AF42, G47, N47, N49, G54, and N53.
I wasn't able to take a snapshot of the shading, it didn't show up in the Snippit tool image.
I wish to fill cells with black colour for service days staff are not selectable.
The column Entitlement is a formula working on the year start date compares to to the employee start date to work out the days entitled.
What vba or formula(unlikely) do I need to achieve this?
******** ******************** ************************************************************************>Microsoft Excel - Holiday Allowances 2007.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH5H6H7H8H9H10=
ABCDEFGHIJKLMN1*Year*Start01/04/2007**********2OfficeNameNo.Start
DateWorking*Week*Entitlement12345*3****DaysHours********4**************56000Coley*B.27429-Dec-0117*2******66000Cox*D.A.2324-Jul-73537.5*5******76000Elsom*Lucy23304-Aug-0300*1******86000Graham*A.3919-Feb-65537.5*5******96000Graham*G.W.4001-Jan-8217*5******106000Hallam*K.A.15502-Dec-85649*5******Sheet2*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I am trying to write vba code that will highlight the row in the range if a field is over a certain percent. The column number won't change but the number of rows will. I'd also like the code to automatically work on all tabs of the workbook when a button is clicked.
Data Info:
Currently there are 4 tabs, but can have more/less
Columns used are A:O
Data for shading starts at A3 and should go to however many rows have data and ignore blank rows
Formula should be if data in column E is over 10.00% then the data in that row A:O should be shaded in the color off yellow & have black thin orders
If the data in column E is not over 10.00% then the data in that row should not be shaded but still have thin black borders.
When I hide columns in a column chart the different fill colors I used on specific columns no longer show up with the colors I originally had. Is there a way to maintain the proper sequence of column fill colors even when some columns are hidden?
View 1 Replies View RelatedThe logic is that I want the cells to be highlighted and have a border drawn for them as shown in the attached sheets (I have used record macro which will do this for rows 39 and 40 when the click button is clicked). The highlighting should start at the row number specified, stop once a merged cell is reached, skip the merged cell, continue highlighting, stop once a merged cell is reached, skip the merged cell, continue highlighting and so on and on until the last row (which is user specified) is reached. The first row after the merge cells should be highlighted in grey and yellow.
View 9 Replies View RelatedHow do I shade a whole row based on the value in one of the columns?
View 11 Replies View RelatedAt the moment i have print black and white so the cell shading does not print.
I also want to print a watermark but by having print black and white on the watermark is coming out white so you cant see it.
Is this possible so i can have cell shading on, but not print the cell shading and have a watermark that does print.
The watermark needs to be text not an image.
It's been about a year since i've used VBA and i need a quick piece of code that will Fill/Shade any cell in my worksheet Red (#FF0000) only IF the date is equal to Today or less...(I need to highlight expired cells)...
Im in Australia, and i know there's an issue between the Aus v US date in Excel...i believe there's a piece of code that can rectify this...
I am cross referencing products between two sheets. They come with different product numbers and I have modified them to search for the important last 5 digits from tab 1 (image 1 column D) with the full 11 digits in tab 2 (image 2 column E). The tab for cross referencing is named Distribution vlookup. I have the following formula and cannot get it to work properly. I have used this formula before in another similar report, and have adjusted the tab/file names and fields but I must be missing something since everything comes up as unauthorized, yet I can ctrl+f and find matching items.
VLOOKUP Formula:
=IF(ISNUMBER(VLOOKUP(D4,'Distribution vlookup'!$E$6:$E$133,1,FALSE))=TRUE,"Authorized","NotAuthorized")
Next, I wanted to nest a color coding on whether the item has sold in the last 12 months (image 2, column H). I cannot get that to work, so I tried to run a seperate column to identify selling status and cannot get that ot work either.
Sold in last 12 months formula:
=IF('Distribution vlookup'!H7>0,"Sold in last 12 mos","n/a")
I am trying to copy a selected cell but instead of just a copy past, I want to auto fill where it will increment the number.
ActiveCell.Offset(-1, 1).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
' Selection.AutoFill Destination:=Range(ActiveCell.Offset(0, 0)), Type:=xlFillDefault
' Selection.AutoFill Destination:=ActiveCell.Resize(ActiveCell.Offset(0, -1).Value, 1), Type:=xlFillDefault
Application.CutCopyMode = False
I have tried the above code but it hangs.
In a workbook I have
Sheet 1 and Sheet 1a
1a is used to provide a start and finish date for a task - there are many of them
The following is located in a cell in sheet 1 which looks at 1a for the dates etc
HTML =IF(AND($A$427+B$430-1>='1a'!E10,$A$427+B$430-1<='1a'!I10,B$429<>"S"),"X","")
The end result is an X located in the respective feilds.
This works fine but.
I have set up the Calendar area in 1 so that when the X is shown it is Red.
The sheet also shows a Blank in both the SS [Sat and Sun]
What I would like to do is to have instead of the blank in the SS [Sat & Sun] a shaded grey which would clearly indicate that this was weekend.
What I am after is the weekdays with the X as now and the weekend only in a grey shade as it is at the moment without the X
Can I automate an excel worksheet such that if ,say, "436" is written in a
certain cell it shades that row yellow, or if "437" is in that cell it
shades the row blue. It would then be easy to visually tell in an excel
chart which lines belonged to ,say, specific companies.
I need a macro that will clear the contents of all cells shaded white in range c1:c20.
Clear the contents, leave the cells shaded white.
can i make a cell dark green if it is value 1 and very light green if its 10 and all varying shades of green in a linear fashion in between?
View 9 Replies View RelatedIs there a way to shade alternating rows in a range and have it always be alternating no matter if a row is added in the middle of the range. If I do it manually now and add a row it messes up the shading and fixing it manually is very time consuming.
View 7 Replies View RelatedWhen doing a normal copy and paste of a formula you get 1 cell added to the formula
ie... a1=b1+c1
when dragged and copied to a2 you get a2=b2+c2
Is there a way to make it add more than one cell so that when the first formula is dragged and copied I would get a2=b17+c17
The actual formula I am doing a copy and paste with is:
=IFERROR(CHOOSE($I$2,'YTD & MTD'!D58,'YTD & MTD'!E58,'YTD & MTD'!F58,'YTD & MTD'!G58,'YTD & MTD'!H58,'YTD & MTD'!I58,'YTD & MTD'!J58,'YTD & MTD'!K58,'YTD & MTD'!L58,'YTD & MTD'!M58,'YTD & MTD'!N58,'YTD & MTD'!O58,'YTD & MTD'!C58),"")
I am needing to copy and paste to the next row but want 'YTD & MTD'D58 to change to 'YTD & MTD'74
I'm using 3 sheets within one Excel file. 1st sheet (target) is called Offer, the 2nd one (source) is called Stock and there's a 3rd one that only contains a button to which i'll assign the final macro. Now, back to it.
I need to copy from Stock to Offer contents of the entire column (basically there are a few more in Offer, so i need to copy each one and place it in a different part). I need to copy from ROW 2 and paste in ROW 6. The length of the document varies, as it's connected to the database (now i can have 3800 rows, in the next hour 3765). Previously i just added an X to row 4500 ( i don't expect to EVER have more than 4000 lines) and just copied from 2 to 4499
Second issue is, on the Offer sheet i have to fill a column with a certain value. Again, it needs to start from Row 6, and needs to be as long as the adjacent column. To get a better idea, i have for example 3800 price lines, so i need the column next to it to be filled with 3800 rows saying GBP (this will be a fixed value).
Also, none of the codes above should exclude blank cells in the middle of the string. Last thing, seems some of the barcodes i have only have 12 digits, so i'll need to add a 0 in front of all shorter numbers to go up to 13 digits.
[Code] ......
I should be able to figure the rest by recording bits and pieces. The Save part is done already, i'm using
[Code] ....
I needed it in XLSX format, not XLSM so that suits just fine. I might need to add hour and minute as well but i can sort it out (as i said, i can do some stuff on my own but this one got me to the bottom).
I currently have some code that copies a single cell from multiple worksheets. Unfortunately those cells all have a yellow fill and i don't want that fill showing up in my summary sheet. How can i alter this code to copy the cells without the fill.
View 2 Replies View RelatedLook within sheet "highlowperf" column K and copy the row containing cells that are filled green in column K into sheet "overallperfomers" starting from row 9 within the "overallperformers" sheet until completed. It needs to be from row 9 as i have other data above this which i need to keep intact.
Further to this i do not require all information from the row that is found to contain a green fill. I wish to copy columns A B C D E F G K.
If this second part is not possible I can settle to just copy the whole row over and i will try and work around.
I have a formula that is averaging seven (one week) consecutive cells, I want to Copy/Fill the formula down the worksheet for the entire year. Is there a way to use the fill handle to drag down and make the formulas use the next seven colums?
I have removed the $ from the row numbers in the original formula, but when I use the fill handle it fills like this:
=average(B2:B8)
=average(B3:B9)
I would like it to fill like this:
=average(B2:B8)
=average(B9:B15)
I have some formulas that combine template text and values from cells in that row to result in text that I then Copy and use in another place. I am trying to convert that to a Macro.
My Skills are Novice -- I have searched Google and Tutorials and it seems that my usage is not common so, I have not found answers to help me move ahead.
I'm working with a simplified version of what I want to do to eastablish a
Working model that I can modify and expand. I had a macro that ran to completion but I abandoned it. I realized that I was on a Wrong Track. It was created from recorded macro sections and googled routines and very clumsy.
Heres my Scenario
If my Excel Cells were
Cell A1 = 135252
Cell B1 = $13.95
Cell C1 = 3
Cell D1 = Oversize
I want to run a Macro from E1 that would Result in text of
Our Stock Number is 135252 - The Price is $13.95.
We have 3 in stock. Type of Packaging is Oversize
being left in Cell E1.
I would like the Macro be able to run on other rows as well when launched from Cell E of that row.
My Goal would be to make the macro work on a range of rows by Highlighing Cell E in those rows.
My Excel Formula to accomplish the same thing is -
="Our Stock Number is "&A1&" - The Price is "&B1&".
We have "&C1&" in stock. Type of Packaging is "&D1&"."
If I could get this much working, I think I can expand and edit to encompass my actual application.