How To Apply Formatting / Macro To A Worksheet Before Opening
Mar 11, 2014
I have software that exports to an excel file. The files are one or two page reports. The way the software dumps the reports into excel, it comes out looking pretty rough. The rows and columns aren't spaced very well, the fonts look like garbage, and nobody likes them.
I would like to apply formatting changes to these reports automatically without the end user having to do anything. So as soon as the document is opened up it bolds the column headers, adjusts column widths etc. The reports are auto generated so no formatting can be done when the reports are made.
There may also be different formatting done to different reports. The files will come out named something like, "Report_A_DDMMYYY.xls, Report_B_DDMMYYY.xls," etc. Report A, and Report B will likely need different formatting.
I'm not opposed to using VBA or whatever might be necessary to make this happen. I can learn anything I need to pretty quickly, I just don't know where to start with this.
View 4 Replies
ADVERTISEMENT
Feb 29, 2008
I'm trying to find a macro to apply conditional formatting to a large number of cells,
What I would like to do is when cell E96 has a value of a, cells E3:F95 are shaded in grey. Then when cell G96 has a value of a, cells G3:H96 are shaded in grey, and so on down to IU96 having a value of a and cells IU3:IV96 shaded in grey.
View 9 Replies
View Related
Aug 31, 2006
I need to apply the following code to all the sheets in my workbook (they are all identical format)
rivate Sub mymacro1()
Application .OnTime TimeValue("10:27:00"), "MyMacro1"
Dim objOL As Object
Dim objItem As Object
Dim lngRow As Long
Set objOL = CreateObject(" Outlook.Application")
lngRow = 6
Do While activehsheet. Cells(lngRow, 1) <> ""
If ActiveSheet.Cells(lngRow, 6).Value < Date Then
Set objItem = objOL.CreateItem(0) 'constant olMailItem = 0
With objItem
.Body = "The training review for employee: " & ActiveSheet.Cells(lngRow - 5, 2) & " is due today """.....................
I don't know what syntax to use to 'globalise' if you like the macro to perform the action in the code to all the sheets.
View 4 Replies
View Related
Mar 6, 2014
I'm having a problem to use the macro for opening a specific worksheet in a workbook. My plan to setup the workbook to open the first worksheet no matter which worksheet that was last saved and close.
What i have in mind is like below macros but it is not working.
Private Sub Workbook_Open()
Worksheets("Main").Activate
End Sub
Attached is the sample for the workbook. Test file.xlsm
View 3 Replies
View Related
Nov 23, 2013
I want to apply conditional formatting by VBA. Cell Q2 contains the month number, (in this case 11). I want each cell in the range D2:D50 to be filled in red and the word 'UNPAID' appear if Q2 is greater than 10 and the cell is blank, otherwise no formatting at all. I'd like this to be triggered on the Worksheet_SelectionChange event if at all possible.
View 4 Replies
View Related
Sep 14, 2012
I am trying to create a macro (which will go in an add-in, using Excel 2007) which will apply a custom format to any selected cells which have their formulae hidden (Format Cells, Protection, Hidden). A similar macro works fine for locked cells.
Here is the UDF I wrote, which returns True/False based on the Hidden status of a cell:
VB:
Public Function Hidden(Check_Cell As Range)
Hidden = Check_Cell.FormulaHidden = True
End Function
[Code]....
just place all three pieces of code into a module, and change the first line of the two macros to a standard "Sub Macro1()" type format.
View 9 Replies
View Related
Aug 31, 2007
I have this macro and all I want to do is apply it to every page of my worksheet with a loop and can't find what kind of loop I need:
Sub format()
' Keyboard Shortcut: Ctrl+r
'
ActiveCell. Offset(-37, 0).Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(37, 0).Rows("1:1").EntireRow.Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Range("A1:K37").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
View 3 Replies
View Related
Jul 3, 2014
I have a workbook that contains 168 sheets of data (it's an extract from a PM tool) which is effectively a status report from each project in our portfolio. Contained within each status report are some financial data that shows a Plan number and a Forecast number for which I want to apply conditional formatting to this section (this is the same section for each sheet), to all the 168 sheets without having to go individually into each sheet. I have searched here and all the varying responses to a similar situation as mine, do not cater for the number of sheets that I have. And I need to do this on a monthly basis at monthend. So in the example below I want to apply conditional formatting if the Forecast (Cols D & G) are greater than Plan (Cols B & E). Is there a way of doing this just with the conditional formatting or would it need a VBA script?
Col A Col B Col C Col D Col E Col F Col G
Financial Summary - Selected Project Currency: USD
Current year total cost
Overall project cost
[Code] .....
View 1 Replies
View Related
Apr 29, 2013
Column A has 200 names.
I want the names John, Tom, George, Frank and BillyBob to be filled with blue.
I want the names Sally, Mary, Kathy and Gertrude to be filled with pink.
I want the names Ebenezer, Vader and Fagin to be filled with black...and so on.
View 5 Replies
View Related
Dec 31, 2013
I wish to have one conditional format apply to K and L. Presently it's L only where it works.
=OR(K1="",AND(K1="No",L1<>""),AND(K1="OJ",L1=""))
Why when I change the range from =$L:$L to =$K:$L does it not apply to both?
View 3 Replies
View Related
Feb 4, 2014
is possible to construct a conditional formatting scenario and at the same time apply that formatting if the cell entry is within 10% either way of the logical test?
For example if you apply conditional formatting if the cell value is between 300-500 is it possible to add in a 10% swing on each value?
View 3 Replies
View Related
Jun 27, 2014
I have a conditional formatting issue, using VBA.
My conditional formatting code works fine for one column; however, when I try to apply the conditional formatting to two columns, the code only applies to the first column listed. Additionally, I have two sets of "rules" that apply to the same column. One of the "rules" includes two columns. The other rule applies to only one column. I'm not sure if this is my issue, or whether I'm trying to apply the same code to two different columns.
My code is below.
Code that applies to column "M" only...
View 3 Replies
View Related
Nov 26, 2005
I downloaded the checkbook register from Microsoft.com and am trying to add a
few cells at the end to quickly calculate my outstanding debits and credits
so that I can balance my statements automatically. I figured out how to write
the formula properly (eventually), but the cells will not take on the
currency format no matter what I do. I have tried pasting the format from the
other cells within the register, tried clearing the format and reapplying the
currency format, tried accounting, etc.
View 9 Replies
View Related
Jan 20, 2008
I have a worksheets ( named TRACES) that contains 96 scatter charts in (4 groups of 24)and 8 line charts (1 group of 5 and 1 group of 3).
I would like to create some VBA code to loop through the different groups, the graphs in each group needing the same formatting.
how to do the looping for each group with or without hardcoding in each of their chart names.
View 9 Replies
View Related
Aug 29, 2006
I have created an excel file that contains a lot of worksheets which contain a lot of raw data and charts. There are 5 different tabs for 5 different companies. Each tab currently contains only 1 year of raw data for that company. Then I have another excel worksheet that has nothing but charts on it. Each chart is a bar chart that shows each of the 5 companies. It also shows the current rate for the month for each company and the 12 month average for each company. On the chart there are also 3 lines. There is a standard line that we try to meet each month. There is also a line for the upper control limit and a line for the lower control limit. Here is my problem:
The 2 bar charts (current rate for the month & the 12 month average) are created and updated by the raw data in each of the 5 data sheets.
Based upon the values in each of the cells that create and update the bars on the charts themselves:
I want the bars to be RED if the values are below the lower control limit.
And I want the bars to be GOLD if the values are above the upper control limit.
And I want the bars to be green if the values are in between the upper and lower control limits.
View 2 Replies
View Related
May 19, 2014
Column b in sample is conditionally formatted based on it's values. I want to also apply that same formatting to the person's name in the chart in D2:I9. For example, Jeff is in bottom 50% so cell B2 is shaded red with red text. I would like to apply that same red shade and red text to all the cells in my chart that say Jeff. Also, as example, all of the cells in my chart that say Kelsey would be formatted with green shade/green text and so on...
View 1 Replies
View Related
Jul 13, 2013
I have a table. I want to apply conditional formatting to the entire table so that wherever a cell contains a dropdown list (validation list) the cell is formatted with a different colour.
View 3 Replies
View Related
Nov 30, 2008
I'm trying to apply conditional formatting (shading) to cells that are left blank.
(Purpose: I am designing a research template for a client to complete with data and want the spreadsheet to show them where they've "missed a bit"!)
(When I go to the conditional formatting box, it asks me to specify when "cell value is"..."between/not between/equal to/not equal to" etc. But there's no option to specify when the cell is blank.)
View 2 Replies
View Related
Aug 11, 2012
Is there a way to apply conditional formatting to a range of cells that contains formulas?
View 6 Replies
View Related
Mar 24, 2014
I have a spreadsheet and I want to color particular cells in a column with a new color - i.e. any new changes need to be highlighted. I know there's a way to do tracking changes in excel, but it just sticks a little flag almost invisibly in the corner of the cell. I want to be able to bring the spreadsheet back to our administrator and say hey the stuff in red is new.
On a related note - I am working on this massive spreadsheet that is a .csv but I am saving it as an exel spreadsheet - is that ok? I am assuming that if I save it as a csv, it will return to the original formatting just without the colors, filters, etc changes I made - which is fine because I think somehow the .csv file will be uploaded to the system and no further changes need to be made.
I found out the hard way when you have a .csv file and make changes and then save it, you lose all the fun row/column size adjustments, color, etc - but I figure in the meantime I'll work on it as a excel spreadsheet and then return it to it's natural .csv file status.
View 1 Replies
View Related
Jul 3, 2014
I'm looking to see if there is a way to take the decimal place formatting of cell and apply it to other cells.
For example, if someone types in 0.0001 into a source cell, I'd like to take that decimal formatting and apply it to other destination cells. This way when values are typed into those cells it will automatically display 4 places past the decimal, no matter what the value. ie 50 will display 50.0000
View 7 Replies
View Related
Apr 25, 2014
I am having trouble getting some conditional formatting to apply to all cells in a column in a pivot table. Currently, the conditional formatting is only applying to the top level items in the pivot but is not applying to the lower level items. I can see why it is doing this. the range in "Applies to" is only specifying the rows that contain the top level items. I tried to change the range to D10:D647 but, it reverts back to just the top level items. How to get it to apply to everything?
Image attached : Capture.JPG
View 2 Replies
View Related
Dec 4, 2012
I know how to set a conditional for a cell to change a color when i apply a specific value, but how about if i want to set E2 cell to change to green when i place a 'X' on F2?
Pretty much I want 2 columns that say Yes and the Other No. When i place a X on Yes that other cell turns green, if i place a X on No that other cell turns red.
View 5 Replies
View Related
Nov 5, 2004
Is there any way to create a default template that will apply all the same formatting, print options, etc to every new workbook that I create. I guess what I am asking is a way to change the excel defaults for text type, border size/color, etc.
View 4 Replies
View Related
May 22, 2014
I m unable to apply conditional formatting with numbers ( font and fill of same color).
like:-
if press 1 , cell and font should be of same color and if 2 with different color and so on
as of now either formula is applying on cell or font but not on both
View 1 Replies
View Related
Aug 7, 2008
I'd like is for an automatic job to be set up on my machine (windows) that opens the CSV files, formats them, then closes the file (as a .xls doc). So that anyone subsequently opening the file opens a formatted spredsheet.
I tried setting up the auto_open macro function, but I have trouble because I need to select the data (which is in delimited csv format) before I run the formatting routine, opening the file and running the file can't cope with this because the selection (Col A) has not been made even though I include the column selection before the formatting when recording the auto_open macro.
the error reported is:
runtime error 1004:
no data to parse
So is there a way of doing this??
To format the data, I use the following steps:
1, Select column A
2, Data>Text to columns>delimited>~ (~is the delimiter)
3, Column data format text for col A & B
4, Select all (top lh box above cells)
5, auto column width (double click on a column boundary)
6, select columns C to I
7, centre justify
example of csv data:
Site~Pipe~:Lsno~:MMSSid~:MMSControl~:MMSStatus~Bore~Pspec~:CTLTargetted
/PTE2D~/ADMIN-TEST~730001~-6727~YES~0~50~13459N~60%
View 10 Replies
View Related
Apr 15, 2014
I started cycling and I'm wanting some data. Referencing the below attached document, I want to take the info on Tab "Log - January 2014" and apply that information to Tab "Stats - Ride Type Data". I was able to use the countif formula for things such as temperature, wind, and feeling, but after staring at the computer for hours I cannot figure out how to apply that data to specific types of riding. I am wanting to know how many miles I travel via commute, road, or townie (shopping).
Cycling Log.xlsx
View 1 Replies
View Related
Jan 27, 2012
I am trying to use the status of Conditional Formatting to toggle on/off protection for a cell.
I have a cell with Conditional Formatting applied if the result of a formula is true. If the Conditional Formatting is applied, I want the cell protection turned on so a user can not change the entry in the cell. If Conditional Formatting is not applied (false), cell protection is turned off and the user may edit.
I am using Excel 2010 and Vista.
View 1 Replies
View Related
Jul 29, 2014
I'm using the following VBA code that highlights a selected cell or range of cells when selected (to more easily see what cells I've selected).
[Code] .......
I'd like to use this code on every worksheet within every workbook that I open. Right now I've only inserted the code in "This Worksheet" in a single workbook and that's the only place it works. I tried creating an Add In with the code so that it worked in all workbooks, but it doesn't work.
View 2 Replies
View Related
Oct 19, 2005
I have several worksheets in a workbook that has dates of completion of
various training topics. Some of these topics require annual (12 months),
bi-annual (24 month), 6-month, or even 2-month re-training.
What I am trying to accomplish is to automatically shade all cells for dates
that fall within one month of these periodicities based on the computer's
clock when a workbook is opened. Unfortunately different columns have
different periodicities so what I was planning on doing was putting that
monthly number in a cell ("12" in A1000, for example for all Column A
values). When the file is opened, if any of the dates in column A are more
than 11 months from today's date (A1000 minus 1), they are within one month
of needing to be re-trained and I want the cell shading to be a light
yellow.
View 11 Replies
View Related
