I use excel 2003. To improve readability, I have formatted each line to have a slightly different background color (white for odd ines, light gray for even). Sometimes when I edit, I use Control + D to fill down a column with the data or formula from the 1st cell. However, when I do that the background formatting is copied too and it messes up the alternating colored lines. Is the a way to fill down only the data/formulas and not the color/pattern?
I am trying to copy a countif formula down a column using the fill handle, but when I copy the formatting down the column the range changes. Is there a way to make it so that only the variable changes and the range stays the same?
I am trying to fill in reference cells horizontally but fix the column and increasing the row number as it fills. For examples I want to start my refence as "=A1" in cell B1. I want to copy this reference horizontally to column Z1 but want the refences to be A1, A2,...,A26. Excel only seems to increase columns when filling horizontally and rows when filling vertically. Transpose works but it's an array which would slow the spreadsheet down.
I have to copy the formatting from "rows G&H" for 40,000+ lines. Is there an easier way than doing the formatting then highlighting the first two cells (G4 and H4) then dragging the bottom right hand corner down 40,000 lines?
Is there a way to copy the formatting of a cell automatically? For example, if you just do "=B1", then the cell with this formula will automatically update with whatever cell B1 contains whenever it is changed.
But is there a way to do the same thing with formatting? So if cell B1 was bolded, or changed the cell color, or conditional formatting, is there a way the cell that is referenced to B1 would also automatically change to whatever the formatting change is?
I am having trouble copying conditional formatting down my spreadsheet. When i copy it the number dont change. the formula i have in the conditional formatting is =O3
Cell (A1) is formatted differently from the other cells of the same column. I added to it a conditional formatting (based on a formula) which I want to copy [alone] to the rest of the cells, without getting the other different formatting of cell (A1) copyed as well.
I am working on a check box based list for work. The requirement is for the check box to be linked to the cell that it is in and then for conditional formatting to be done so that if the box is checked it goes green and if it is blank it goes red. This is fine for individual cells, but when i try and copy the formatting and cells throughout the worksheet it links all the cells back to the original cell.
I have a set of values on Sheet1. These 'original' values will never change.
I then have a set of values on Sheet2. The 'updated' values get updated every month.
On Sheet3 I want to show the updated values again, pretty much a copy of Sheet2, but where a value is higher than that on Sheet1 or lower than a value on Sheet1 I want to use conditional formatting to show this.
Is there a simple way to use conditional formatting in such a way? I have a long list of values so don't want to individually create a rule for each cell.
Below is a code that works for this attachment Invoice.xlsm When you hit the code you will see how the 2 left most columns disrupt the conditional formatting resulting in one color for those columns as opposed to every second row with one color and the rows between them another color. modify this code so that the conditional formatting remains the same? (I highlighted the part in the code that needs attention).
I have written in some conditional formatting to have a cells automatically updated based on a response in a cell above. That is: If a no response is chosen to a certain question, then all other questions in that range are to also be a No response. I have attached the document im working on with an outline of what the problem is!!!!
I have 10 rows of 6 numbers, all between 1 & 49, in the range A1:F10. I also have the range A20:AW20, which are all currently blank cells. When I type a number between 1 and 49 inclusive into the range A20:AW20, any number that matches it in the range A1:F1 I want the cell to fill in colour.
is it possible, after inserting new sheet rows, to automate the copying of formulas and cell formatting into the newly created space, instead of manually copying the formulas and formatting down into each column?
I have a piece of code that runs through various excel files and takes the data (minus the header) and pastes that into a separate workbook. The piece of code that does the actual copying i think is this (i have used code from the msdn website)
Code: With sourceRange Set destrange = destrange. _ Resize(.Rows.Count, .Columns.Count) End With destrange.Value = sourceRange.Value
I have tried replacing the sourceRange.Value with sourceRange.Text however when i do that it will copy nothing any more.
I have information in A1. I want A1 to look at B2:B5 and D2:D5 (Under conditional formatting) and if there is one or more "X" in those cells to color A1 Grey (Fill). I keep trying to put a formula under conditional formatting, but it is only working for the first cell (B2) and not the rest.
The spreadsheet I've been working on is functionally working how I would like, but I am looking to improve it cosmetically. I have a range of cells in which I input scores. Those scores are averaged in blocks of the value of cell B1. I would like to fill the cells yellow if they are a multiple of the value of B1.
I'm trying to discover the best way to accomplish formatting a cell by filling it a certain color based on 2 of 3 criteria of other cells.
Column A has the title of the field. Cells B1:D1 have one of 3 values: Nothing/blank, Complete, or x. If all of the x's are gone within the range of B1:D1, I'd like to automatically format cell A1 to fill in Blue (or any color).
ive got the following code looking to delete the entire row that contains cell H with a red interior fill (colour index 3). it has been coloured using a conditional format rule. When I run the code it deletes all rows?
Sub DeleteIfRed () Dim LastRow As Long Dim i As Long
i am trying to import an excel document and then copy over some columns to another worksheet from the imported worksheet, using a macro. Everything imports and copies over fine except for one column that has cells that comprise of a list of number separated by commas. When these cells get copied over some of the values retain the commas but some of them (specially when there are a lot values separated by comma) gets changed to this format
From: 1,229,124,012,441,230 To: 1.22912E+15
From: 1044,1048,1052,1053 To: 1.0441E+15
From: 1279,1282,1286,1295,1299 To: 1.27913E+19
From:926,929,938 To: 926929938
I am trying to figure out whats happening here! Is there a max number of letters this column can take and if it exceeds that value, excel converts them to this format? Or is the formatting gets changed to something else while copying and pasting?
I have applied conditional formatting to a cell using formulas and i want to copy that formatting to a new cell using similar formula but pointing to different cells. Is it possible to do so? I have tried to copy and paste special>formats, but that just copies the conditional formatting with the same formulas.
Sample: =IF($C$5/$C$295>$R$5,TRUE,FALSE)
I want to copy to $D$5/$D$295>$R$5,TRUE,FALSE) without having to paste the new formula in each time.
I am having a problem with a excel spreadsheet. There are no formulas or anything, just text that I used excel to get everything lined up. I haven't had any issues before, I was copying and pasting within a document. It was all text with different row heights set. I ran into a problem now where the text copies and pastes fine but the row heights aren't coping into the cells I am pasting into, just the text. What do I have to do so they will copy? I don't want to have to go an individually adjust each cell.
When I hit the macro code you see below I get both columns D and E, starting from row 18, to get 'filled down' to the specified spot. Every second row has a conditional format (when a value is entered in column A) to change the row to the color grey, and every row between it has a conditional format (when any value is entered in column A as well) to have the row changed to the color red. The issue here is that the Macro code messes up the conditional formatting and uses the conditional formatting of those two cells, which are being dragged down, for those entire filled-down columns! This is what I am starting off with test1.xlsm and this is what I end up with using the macro code below (or doing it manually)
test2.xlsm
Is there a way for the Macro code to bypass this issue?
How to copy and paste conditional formatting with our changing the cell values. My first row of data starts in row 4 and here is what I have in D4
If D4 is less than or equal to AA4, AW4, BS4, CO4 then it will highlight D4
Now I want to copy that format and paste it for all rows in column D. The problem I'm having though is when I paste the formula it doesn't change to match the row I'm on. For example, when I paste it in D5 this is what I get
If D5 is less than or equal to AA4, AW4, BS4, CO4 then it will highlight D5
How do I paste it so that it will update to show AA5, AW5, BS5, and CO5. I want to be able to do this for 500 rows.
I have a workbook containing two worksheets of staff training records.
The first work sheet contains a list of names and the dates they completed various training courses. I have used some simple date based formulas and conditional formatting to colour-code their name depending on whether their earliest retraining due date has passed, is coming up in the next few weeks, or is a long way off. The data is set out alphabetically, one person per row of data.
The second sheet contains the same list of names, but each person's data is split across two consecutive rows. The cells in column A which contain the staff names are merged in pairs so that the name heads both rows of data.
I want the colour coding of the merged name cells in sheet 2 to automatically copy the colour coding applied to the single name cell in sheet 1, but don't know how.
I have conditional formatting set up so that the cell becomes highlighted if it contains a specific text.
Example of wanted conditional formatting:
Conditional formatting rules manager Rule
Format
Applies to
Stop if true
[Code] ......
For some reason this formatting is inherited by another adjacent cell as we continue to input information.
For example:
Column M is formatted so if 'AP' is placed in any cell in that column the cell fills pink. As more information is inputted into the sheet, the conditional formatting copies to Column N. It does not happen with every entry and I have not been able to isolate the specific steps to recreate the copying. Multiple people use the same sheet and fill it out and needed.
After working with the sheet changes are made to the "applies to" column without people meaning to.
For example:
Conditional formatting rules manager Rule
Format
Applies to
[Code] .......
I would like to make this formatting so that is only applies to the Column M and not "travel" to other cells of the sheet.
