Here'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
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 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.
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 Related1. 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).
apply conditional formats to a cell, based on the entries in another, different cell?
View 2 Replies View RelatedThis is also in sheet1 module ( It cuts the respected row and pastes in sheet2 when date/time is populated in column 14 by double clicking).
[Code]....
How to copy the cell formats of the copied range in the vba.
Code:
Sub Summary_All_Worksheets_With_Formulas()
Dim Sh As Worksheet
Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook
With Application
[code]......
I want to build a database where the user can select a specific time period. I have a static file with the time periods and the related values. For instance I have in column A1:A15 the time periods from January 2011 to Dezember 2011, but I have some dates multiple times as there are more values attached to it. For instance I have in the first 4 rows January 2011...what I want is when someone puts in the value January 2011 in cell B1 and Dezember 2011 in cell B2 that the whole static file gets copied to another location (including the multiple dates) displaying the chosen time period. Similarly if someone puts in the value March 2011 to November 2011, I want only those values to be copied.
View 4 Replies View RelatedIn a macro, I am copying data from another sheet and most of the time I only require the actual data or the values as per below.
View 2 Replies View RelatedIn a single cell, I would like to show 2 values: a currency and a percentage. So A1 and A2 has values, say 50000 and .10 respectively (these are derived via formula, if it's of any relevance). What I ultimately want is "$50,000 and 10%". I've originally thought about adding the "$", " and ", and "%" separately, but I need the currency value to include commas.
View 2 Replies View RelatedI have a workbook that has a lot of formulas in it that reference material stored on my local computer.
I need a macro that will copy all 42 tabs in to a new workbook book without all formulas, but saving all the formats have made. This would save me from every other day coping and pasting manually each of the pages.
Currently I have it setup to copy rows to a sheet "Report" based on a single cell value. But now I need the same thing but have it copy the rows based on 2 cells values to sheet "Report". So for example I wanted to copy and paste each row in my workbook that contain values in Columns N:N that contain the value "Test" and in columns AB:AB that contain "1".
Sub copyagain()
Application.ScreenUpdating = False
Dim sh As Worksheet, findThis As String, fAdr As String, fLoc As Range
findThis = "1"
[Code]....
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.
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)
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 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 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 tried all failed.We have about 160 different workbooks (one for each business unit) stored online. Staff enters information about their weekly revenue and expenses and here at head office I collect that information and consolidate them.What I am trying to do is that;1.) Create a master Workbook with ~160 worksheets (One worksheet for each unit) named exactly the same with other workbooks2.) And macro can pull the information from related files stored in a certain folderIt is very much like another members problem but I am not sure why I cant get that code working for me? [URL]
View 5 Replies View RelatedI 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 come up with the most efficient way to copy data to multiple sheets within the same Excel workbook. The original data exists within one column on a summary sheet (could have up to 500 individual entries). I want to copy each individual entry to a unique sheet (that already exists), but in the exact same cell location within each sheet. I would only want to copy the original data value and not any formatting. Is there an efficient way to do this?
In my example spreadsheet, the original data is on the SUMMARY sheet. Sheets A through J would be the target sheets, with cell B2 as the target location for each of those sheets. My example shows the result of a manual copy paste value process, but I am hoping to automate that.
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.
Conditional 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.