I have a code that copies a template and pastes it into another tab. However, it is copying the conditional formatting and pasting it as well. I already have conditional formatting on the other tab and do not want it to paste another set of conditional formats because the formula's are changing between the copy and pastes.
I have a spreadsheet with a number of columns. In column D there are the following options: 2,3,4,C,T. I want to color the cell if there is data in the cell and column D contains a value.
So for cell L17, my conditional format I have =AND(D17=2,L17<>"") - - (RED), =AND(D17=3,L17<>"") - - (YELLOW), =AND(D17=4,L17<>"") - - (GREEN).
I need to color for values of C and T: but there are only 3 conditional formats permitted. Is there any way around this?
I have a sheet that shows sun, mon, tue........,sat - all 7 days.
Under each day there are 3 cols so 21 in total.
Data is being entered into cells each day but as the week goes on it gets more difficult to match up the col & row, to many numbers.
What I would like is a way to highlight the whole range of cells per day in a different colour but only when data has been entered under a day, so if no data then no colour fill.
I only need 2 colours, 1 for sun, tue, thurs, sat &
1 for the remaining days.
=COUNTA($A1:$C20)>0
This works for one range as a conditional format but there aren't enough options to do the 7 days.
I have a workbook that has several INPUT areas that are composed by many rows and columns. These areas must be unprotected because the user must be able to fill them in with different numbers.
The challenge is that these areas have different conditional formatting to try to prevent the users to make mistakes, but the users always fill out one row and then copy down the others, when they need to be the same. This is not always the case. Then, the conditional formatting is lost.
The challenge is how can I allow the user manually fill out numbers, but they should not be able to copy and paste, in any way, within these different areas in the workbook?
how to combine 2 conditional formats?
I'm trying to shade every other row grey so its readable with this:
=MOD(ROW(),2)
But, at the same time, would like to highlight upcoming expiry dates with this:
=AND(A2-TODAY()>=0,A2-TODAY()<=30)
I am trying to create an Excel spread sheet that keeps track of employees safety tickets and the dates they expire. Is there way to have a cell highlight to a color warning me 30 days prior to the due date listed?
Eg. John has first aid and expires on 11/15/2009. I'd like the cell that has this date to highlight red on 10/15/2009.
I have messed around with the conditional formatting and know how to format the cells color and font et., but I can not figure out the formula to use to get my results.
I am using Microsoft Office Excel 2003 (MS Office Standard)(SP3)
I would like to condition one cell to display various colors, based on the information in another cell. I have attached the file for an example. The cell to be conditioned is A1, and the cell that will specify its color is B3. For example, if the value of B3 is "Red Oak", A1 would be red, and if the value is "Maple", A1 would be green.
I have more than 3 of these that need to be formatted (conditional formatting limits to 3 formats), so I'm wondering if I can do an event macro to set the formatting.
Anyone figure out how to get around the 3 format limit for conditional formatting? If so, I'd really love to hear about how you did it.
View 9 Replies View RelatedI have to color direct payoffs and commissions (green and blue respectively). I have to have a running total of payoffs that have not cleared and one for commissions. I have set up conditional formatting to color the text based on the check classification (A for payoffs and C for commissions). What I am trying to do is write a formula that will count only the payoffs (green text) that do not have a cleared date. I am then trying to do the same for the commissions (blue text). these totals are needed separately so I do not need them combined.
I have tried sumif and sumproduct. I have since deleted the formulas i have written cause they did not work.
I use Excel 2003 and am trying to put together an automatic macro to apply five conditional formats to a range of cells. The range is H3:H21 and is unnamed. The values in the cells are derived from formulas. These are the conditional formats:
If there is a 1, the background color should be gold/44 and the font should be bolded.
If there is a 2, the background color should be gray 25%/15 and the font should be bolded.
If there is a 3, the background color should be dark yellow/12 and the font should be bolded.
If there is an e, the background color should be red/3 and the font should be bolded.
If there is a 0 (zero), the font should be white/2.
From what I understand, I need to put the code in by using the "View Code" option in the tab sub-menu.
i have a register with items for calibration. I have a column with a date the item is next due cal which is 3 years from date of receipt manually input by myself. say (H64) due 20/11/2010, i would like the cell to be green if todays date is below the date due, but amber if todays date is within 30 days of being due and finally red if todays date is over. i have manged green and red but cant seem to get the between right for the amber.
View 8 Replies View RelatedI've got two pieces of code.
1. cuts+pastes all formulas and formats to another location.
2. cuts+pastes required formulas but not cell formats to another location.
The s/s is over 330,000 rows deep which makes manual changes impossible.
I attach small s/s containing both codes.
What I want to do is copy+paste formulas+formats from cells having a number in the cell below to cells 4 columns along and two rows down. The s/s illustrates this.
- I have a worksheet (W1) which i have filters
- I have another worksheet (W2) that is formatted for a institutional purpose where i must put the things selected in the W1, but the problem is that: in the W1 i have a cell (A1) with some data but in W2 i have a space composed with one line but with several columns and excel says that he cannot paste the information copied in W1 to put in W2.
there is any way to avoid this problem because at the moment i am making copy paste one by one, and it takes to many time and i would like to copy and paste the information selected by the filter in W1 to paste in W2.
I have a spreadsheet containing all sorts of formulas & data in Columns A - H, and a formula copied down in Column I generates a number that drives a few conditional formats in the preceeding columns.
I'm trying to copy-paste the values and formats of Columns A - H into a new workbook with this pretty simple macro...
Range("A1:H195").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
When the formats are pasted across it's pasting the conditional formatting, but they're not displaying correctly because Column I isn't coming over to the new workbook.
Is it possible to copy-paste the cell formats as they stand regardless of whether there's any conditional formatting behind it?
I need to count the number of rows of active clients. I have set up a conditional format that when someone goes inactive dependent on the reason of inactiveness their entire row of information either gets greyed out or a red line through it. Is there a way to count the current "active" clients- or those that do not have these conditional formats applied to them. And this is also dependent on month, so that it does not show the same number each month.
I'm trying to count cells affected by conditional formatting and I need help tidying up my efforts so far.
The numeric values in my table are formatted if they exceed values in corresponding columns on other tables, indexed via Hack #75. The logic for each cell runs someting like this:
if cell is numeric
if column in (E, M, X, Y)
if (value <c1 or > c2)
highlight in blue
end
else ' columns not in (E, M, X, Y)
if (value > c1 or < c2)
highlight in red
end
end
end
My attempt to put together a formula for each cell appears to work, it looks like this for cell B3:
'=AND(ISNUMBER($B3),
OR(
AND(.....................
I have a range A1:D10 containing formulae. I want the cells to change colour depending on the formula result. Conditional Formatting will do the job but only up to three colour so I need to use VBA because I have 6 colours. For example, if the result is 1, the colour changes to blue, if the result is 2, the colour changes to green, etc
I have VBA code which will change the cell colour if I input a value, but not if the cell is relying on formula result. The formulae results are dependent on cells throughout the workbook. The formulae results can be letters or numbers, whatever is easier.
I am trying to paste formats and validations to the current row of my spreadsheet.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim targetcol, ganntfirst, ganntlast As Integer
On Error Resume Next
Application.EnableEvents = False
targetcol = Range("BudgetHrs").Column
ganntfirst = Range("Ganntfirst").Column
[Code] .....
The code in orange is the problem code, and I have a range named format which is actually Row1:1
And I want the formats and data validation pasted into the current row and also the next row.
I have been using conditional formatting for a project in Excel 2007 but as the end users are using Excel 2003, I have had to switch to the following VBA solution as my requirements exceed the standard 3 available conditions. I have looked at using custom formatting but I need to format the cell colour rather than just the font colour.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("C19:IV384")) Is Nothing Then
Select Case Target
Case "0.5", "1", "U"
icolor = 38
Case "C", "M", "P"
icolor = 40
Case "A", "S", "D"
icolor = 36
Case "L", "UP", "C/E"
icolor = 35
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
* When the macro is run on one worksheet, formatting and values are replicated simultaneously on another identical worksheet (not necessarily vice versa).
* As well as formatting cell colour when containing a value, a border should also be added with different colours for the top, bottom, left and right border.
* When the cell contains no value, the borders should return to how they were previously.
For some reason the following code, when executed, pulls a named range from the source workbook (sProj_Name). I feel like I have used this exact same code before and not encountered this issue. This named range is linked to the source workbook so if I save the file and re-open I get the wonderful 'would you like to update links' pop up. I cant figure out why, when I am forcing it to paste only values and formats, it is picking up this named range. In addition the source workbook has way more than one named range, so why it doesn't pull all instead of just one is a mystery to me as well.
VB:
With Workbooks(sProj_Name).Worksheets(2)
.Cells.Copy
Sheet2.Cells(1, 1).PasteSpecial Paste:=xlPasteValues
Sheet2.Cells(1, 1).PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False
End With
my macro pulls download in on sheet1. On sheet2 it makes a pivottable of it with horizontally displayed the suppliers and vertically the codes of products. This is of course dynamic (one month it may contain 10 suppliers + 8 products, other month 15 suppliers + 20 products). On sheet3 is the (static) lay-out of all suppliers and all products. Now, what I want is that all fields <> empty (or zero) from the pivot table are to be copied and placed in sheet3, the 'report' I have to fill in. index and match won't do the job I think.
View 2 Replies View RelatedHere's my problem..
if i input 75 in cell G2, the value in A7:E7 will paste or will display in A2:E2
or
if i input 76 in cell G2, the value in A8:E8 will paste or will display in A2:E2
or
if i input 90 in cell G2, the value in A9:E9 will paste or will display in A2:E2
or
if i input 78 in cell G2, the value in A10:E10 will copy or will display in A2:E2
See my attachment.. sample prob.xlsx
apply conditional formats to a cell, based on the entries in another, different cell?
View 2 Replies View RelatedConditional Formating Text equaling to Number formats:
Example:
C20 is '436.59 (its pasted as text often is must remain as text) and D20 is 436.9535
I want D20 to have only 2 decimals and turn green if it equals C20. And turn red if it doesnt equal C20.
I am using excel 2007 - I need the extra available columns in Excel 2007 for my application.
I have a spreadsheet that has a significant amount of conditional formatting. I want to copy a range from this spreadsheet to another spreadsheet. In the new spreadsheet I want to remove the conditional formatting rules whilst retaining the actual formats those rules generated.
Format painter and "paste special - format" copies any formats as well as the conditional format rules.
Is htere a method within Excel to do this or can someone suggest a workaround?
1. In whatever cell is selected when the macro is run, enter a new row.
2. Copy the information from the row directly above the new row and paste (values, formulas, formats, etc) into the new row.
3. Return to column P in the new row, i.e if the new row is row 11, then return to P11, for row 12 return to P12, etc.
I have tried recording the macro but because it is hard coded to specific rows, its not working. I have attached a sample copy of the sheet (had to zip due to the size of the file).
The macro firstly formats the data that is pasted into it from an access database, then it does some calculations to determine when 10 working days from a specific date is, and when 20 working days from the date is (these go in new columns at the end of the data). The macro will also add new columns which say "Not resolved" if there is no date in the Resolution column, "Yes" in the "10 working days met?" column if the condition is met and the same for 20 workings days in a different column. As the colouring etc takes a long time I really wanted to add as conditional formatting to the macro!
how to attach a spreadsheet here then I can show you what the outcome we want is. A point to note is that there is not a set number of rows each time we do this, and I don't just want 1 cell to be coloured - I want to specify which cells in the row are coloured depending on the results in another cell on that row.
I have a cell with seven conditional formatting formula rules that I now want to copy to the rest of the column. I can copy/paste special/formats one cell at a time but if I try to to this with a group of cells, (or try using the format painter), it treats the formula references as absolute, even though they aren't shown as absolute in the rules manager. Am I missing something? Using 2007.
View 4 Replies View RelatedI currently have a macro that outputs data to a bulletin-board type table in my worksheet. At the moment, what happens is that the macro copies the entire table down one row using copy/paste, then prints a new line of data to the top row of the table.
This works great because it is very fast and because I only have to format each line once--the formats just get copied down every time a new line of data is added.
My problem is that while this program is running, I am unable to use copy and paste in windows, because the copy/pasting from the macro overwrites the windows clipboard.
Is there a way for me to "copy" formats from 1 range to another range of equal size without actually using copy/paste? I know this can be done with values (eg. range("B2:B4").value = range("A2:A4").value), but I can't get it to work with any sort of formatting.
I am having some trouble with a piece of code that is part of a larger macro. I have a list of data and I need to copy and paste the rows of data in which the values in column B are the same and then delete these rows from the original sheet. The code that i have come up with so far is as follows:
Sub Retreive_Particular_Rep_Invoices()
Dim RepInvoices As Long
Sheets("All Invoices").Activate
Worksheets("Current AM Invoices"). Range("A2:T65536").ClearContents
With ActiveSheet
For RepInvoices = 1 To .Range("B1").End(xlDown).Row
If .Cells(RepInvoices, 2).Value = .Cells(RepInvoices + 1, 2).Value Then
.Cells(RepInvoices, 2).EntireRow.Copy Destination:=Worksheets("Current AM Invoices").Range("A65536").End(xlUp).Offset(1, 0)
End If
Next RepInvoices
End With
End Sub
I know that this is not correct, but it's the best i have been able to come up with so far and thought that i would try and speed it up. I have also attached a spreadsheet with some basic data that i have been playing with as well.