Copy Or Link Formatting From Conditionally Formatted Cell To Another Cell?
Aug 12, 2014
I have a column who's content is determined via about 6 nested if statements from data on that row. That cell is then conditionally formatted to a certain color based on the text that is ultimately printed from the nested ifs (simply an extra visual legend for the text). All of this contributing info and about 1000 lines items make a very large and difficult to print page. What I am trying to do is a make a summary sheet that simply takes the index number of these 1000 rows and copies or links the conditional formatting of the mentioned cell onto this number on another sheet. I have already linked this status cell and put it adjacent to the index number which works well in that with two columns I can show the index and status but if I could combine the color of the status cell onto the index cell, it would be even better. I am pretty sure another conditional format for this summary sheet would not be possible or be extremely complex since the contents of the index cell I am conditionally formatting have no bearing on the conditions for the format. Was hoping there is some VBA magic that could simply mimic the conditional formatting from one cell and put it on another.
View 1 Replies
ADVERTISEMENT
Jun 30, 2009
Using 2007, I need to conditionally format a cell colour based on 5 other cell colours that have been conditionally formatted. The 5 other cells will be coloured either red or greem. What I want to do is have an overall status cell that would be show green if all of the other 5 cells were green, amber if 4 of the other cells were green and one was red and red if 3 or more cells are red.
View 4 Replies
View Related
May 3, 2009
This is my first use of the forum as I only joined yesterday after a recommendation from another colleague.
I am attempting to write a macro to clear the contents of cells in a range that have conditional formatting. Below is the formula I have used...
Sub clr()
Dim r As Range
For Each r In Range("09:050")
If InStr(r.Interior.ColorIndex, "40") Then r.ClearContents
Next r
End Sub
While the formula runs without error, it does not clear the contents, although I read on another thread on this forum that you cannot clear contents of cells that are conditionally formatted.
Alternatively, if there is a more efficient way to clear cell contents (without using a formula in the cell itself),
View 9 Replies
View Related
Aug 5, 2014
spread sheet that I need to do for work. Unfortunately I do not have excel on my home computer to be able to attach a spread sheet
The spreadsheet has one column (A) with a list of questions. The column next to this (B) has either a red or yellow cell in each row (a red cell would be a high risk to the business if the answer to the question in that row was no, and a yellow cell indicates a moderate risk to the business in the answer to the question in the row was no).
The third column (C) is conditionally formatted so if a 'y' was placed in any of the cells they would turn green. If 'n' is placed in any of the cells the cell would change to either red or yellow (this would depend on what the colour was in column B).
I need to know a formula to count cells by colour. So the number of red, yellow and green cells in column C would be counted automatically into a totals box for each colour at the bottom of the spreadsheet. I have tried some online suggestions but couldn't get these to work for cells that had been conditionally formatted. I'm not the most experienced person with spreadsheets (this time last week I couldn't add two cells together)
The final thing I need from the spreadsheet (and I'm not even sure if this is possible) is for a total box to be colour co-ordinated based on the number of red, yellow and green cells in column C. I would need the total box to be green if all column c is green, yellow if three or less cells in column C are yellow and red if any of the cells in column C are red or there are more than 3 yellow cells in column C.
View 2 Replies
View Related
Oct 22, 2013
pivot table?
Is there a Color Function type vba code that can give me the color integer? I have one that can do that for cells that I've manually colored but I can't find a code that works on conditionally formatted cells.
View 1 Replies
View Related
Aug 31, 2012
I have a range of cells that change colors with conditional formats based on the cell value from high to low. I would like to link the cell color to an object such as a circle or rectangle. When the cell value changes along with the conditional format, the color of the object will also change.
View 3 Replies
View Related
Dec 22, 2005
I have 7 rows in the same column of type percentage. I'd like to turn
the text of the highest valued cell red. How exactly is that done with
contional formatting (vs. IF statements for each cell)?
View 10 Replies
View Related
Dec 10, 2007
I have columns that have different colors, depending on there conditional format i.e. Green or Red. I want to count the number of either Red or Green in a total at the bottom. I have search and found a few answers
Function CountColor(rColor As Range, rSumRange As Range)
Dim rCell As Range
Dim iCol As Integer
Dim vResult
iCol = rColor.Interior.ColorIndex
For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = vResult + 1
End If
Next rCell
CountColor = vResult
End Function
BUT these don't seem to work on conditionally formatted columns
View 4 Replies
View Related
May 1, 2014
I am looking to produce a Macro to select conditionally formatted cell's from a worksheet, i got as far as selecting those cell's but i need it to only select cells that have been filled.
View 6 Replies
View Related
Aug 18, 2009
I have a series of conditionally formatted cells which turn the text in them to red if the value is less than 40. This works fine.
Occasionally however a value of less than 40 will need to be entered along with the letter 'v'.
ie. 39v
I'd still like this to be coloured red, but it's obviously coming out as black.
Is there a way to sort this out?
edited to add : Im actually using a separate cell to enter the value 40 (as the value can change).
View 7 Replies
View Related
Dec 10, 2012
As seen from my attached file,
The colour of the percentage cell changes according to its percentage. I would like the S/N cells to have the same colour as the total percentage cells automatically. (Even when the percentage updates)
Secondly, for the cells under "Target", if the target dates are 1 day before today(the current date on a particular day) and the actual date is not filled, the cell fill will turn amber.
If today is on or after the target date and the actual date is not filled, the cell will turn red.
However, if the actual date is filled, the target date cell will be filled green, overwriting the above two condition.
FormattingHelpExample.xls‎
View 2 Replies
View Related
Sep 22, 2007
I would like to copy cells from one worksheet to another, but have the cells that I paste link to the original document. Is there an easy way to do this?
So if I'm pasting from worksheet A into worksheet B, I would like worksheet B to link to worksheet A.
View 9 Replies
View Related
Jan 24, 2010
I have a Piece of Code that copys a Row in my Spreadsheet and Pastes it underneath the original Row.
The Problem that I am having is that within the Row it Copys there is a Spinner in it. I want the Spinner to be copied also but the Cell that the Spinner is Linked to Says as the Original Cell, it doesnt move Down with the New Row Pasted in.
Here is the code that i have written to Copy the Row and Paste it down a Row.
View 5 Replies
View Related
May 25, 2011
I have a spread sheet and I want to conditionally format rows to be a certain color. That part I'm fine with. But I don't want them to be a set color. I have a "key" of different colored cells that I want to be the fill colors of the formatting. The ultimate goal is that for example the key looked like this
red
blue
yellow
green
then the rows I had would be formatted as red, blue, yellow, and green. But if you were to go into the key and change the first cell from red to purple, then the rows would become formatted as purple, blue, yellow, and green. Obviously I can copy formating by hand using the format painter, but I want it to update automatically.
View 4 Replies
View Related
Mar 25, 2013
Below is my code which isn't formatting the cells it's suppossed to. It looks like it isn't doing anything. I think the issue might be with the highlighted section of my code, but when I go to "Manage" my rules for conditional formatting, excel references the appropriate cell under the "Applies to" section. I am using relative cell references for for the majority of the rest of the code and this section follows a section that selects the correct cell for this conditional format.
Code:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=""ABS()>.005"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority[code].....
View 1 Replies
View Related
Nov 24, 2013
let's say cell A1 is colored blue manually, how can i format cell b1 to be colored red? is it possible in conditional formatting?
View 7 Replies
View Related
Nov 11, 2009
I'm trying to link two different budgets into a third spreadsheet. The final step I'm attempting to accomplish is have an if-and statement reference just the month from a date-formatted cell. One of my departments tracks their budget on a daily basis, i.e.
11/2/2009, 11/3/2009, etc. rather than just 11/2009. I want the if-and statement to return the budget from all days in a given month so i can apply the formula to the whole year, broken down monthly too. I saw a question similar to this one but didn't see any responses so I'm not sure if it can be done.
View 6 Replies
View Related
Sep 12, 2006
way to conditionaly colour code all cells in a grid based on cells in other grid: i.e.
gridA
1 1 2
1 2 1
2 2 1
grid B
12 11 14
34 16 53
35 64 23
i want all cell in grid B to be red if the coresponding cell of GridA is 2.
so here "14" "16" "35" and "64" will be red!
View 9 Replies
View Related
Jun 28, 2013
I have two worksheets, one with detail monthly information and one with YTD information. So let's say the three numbers I want to capture in the YTD sheet are in columns B, G and I on the monthly sheet. January's data might be in B5, G5 and I5. February's data is in B12, G12 and I12 and so on.
On the YTD sheet in cell C2 I link to Monthly!B5 and in C3 I link to Monthly!B12 so cells C2 to C13 on the YTD sheet show the monthly totals from column B on the detail sheet. On the YTD sheet, cells C20 to C31 show the monthly totals from column G on the monthly sheet, so cells G5, G12, etc. And finally, cells C40 to C51 on the YTD sheet show monthly totals form column I on the monthly sheet.
In the past I've always created all these links manually. After creating the links in C2 to C13 on the YTD sheet, is there a way to use a formula in C21 that uses the link in C2 to create a link for G5?
View 6 Replies
View Related
Nov 23, 2008
I am trying to copy a column of cells from one sheet to another, but also want to keep all the formatting. The origin sheet has times, but when I copy these to the destination sheet they are displayed as decimal numbers (using the code snippet below). I can change these back to times by formatting the cells using the format painter after the macro completes but I would like the VBA to do this for me. (using 2002 SP3).
View 4 Replies
View Related
May 16, 2013
I'm basically trying to copy the text and formatting (colour fill) from a single cell into all cells I select. So far I managed to get the auto fill to work but the text only copys to the first cell I select of the selection.
Here's the macro I have:
Sub Macro7()
'
' Macro7 Macro
'
' Keyboard Shortcut: Ctrl+f
'
ActiveCell.FormulaR1C1 = "1 Completed"
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub
View 1 Replies
View Related
Dec 12, 2006
I have a workbook (attached) which has two sheets called “Team List” & “Masterfile Data Dump”.
TEAM LIST
This is the sheet that I want to populate when the data dump is a pasted into the sheet “Masterfile Data Dump”
MASTERFILE DATA DUMP
This sheet is where my data dump will be pasted to, columns A to Q and column R is a formula to work out the FTE
Calculation
The reason I want the list to auto populate is that it will change week on week and is a time consuming task keep updating it. I want to be able to type a Team Leaders Name into cell F2 and it populate the list of the agents in that persons team Cells F4:G18 (Name & Surname) along with there FTE (full time equivalent) in Cells I4:I18.
The problem I have is I have no idea how to calculate the FTE sum as the data dump can have one to five shift patterns (rows) for one agent (See my example on the “Master file Data Dump” Sheet of Joe Bloggs. /I have tried to use a VLOOKUP which I have used but this will only return the first value or first rows value of that agents name.
View 4 Replies
View Related
Jan 18, 2013
Is there any way to make one cell on a separate sheet appear EXACTLY the same, including formatting and values? As in this for example: Copy this value and formatting
I would like to keep the formatting, (color and bold), but I have not figured out how to do this. I read a couple other posts but could only copy the values but not get the formatting to also copy over.
[URL] .....
[Code] .....
View 5 Replies
View Related
May 14, 2009
If I wanted to copy a cells formatting and content (Text) to another cell on a separate sheet, what code would I have to embed? I am a fresh neuling, so give it to me straight.
I have attached an example to this post.
I want to transfer the red and bold attributes of the words within the ( ) in sheet 1 to the cells in sheet 2 that I have linked to the cells in sheet 1 with =.
View 9 Replies
View Related
Oct 24, 2006
I have a cell that I've formatted to dd/mmm/yy, It used to work, but now it doesn't. When I type in 1/2/03, the formatted cell shows 0-Jan-00, The formula bar shows =1/1/2. I seem to have a formula in the cell, but I can't get rid of it. I know it's most probably somethinmg I've done , but I don't know what. I've tried everything I know. I can't seem to re format the cell , and I can't delete the formatting in it. I'm stuffed.
View 9 Replies
View Related
Jun 2, 2009
Can I program column B to show a certain value depending on the presence of a string of text in column A? For example, if A1 contains the word "Barbie", then cell B1 shows the value: "Ken"
View 5 Replies
View Related
Feb 5, 2010
I want to create a 'virtual switch' so that if I change one cell from 1 to 0, that a formula in another cell becomes active. I can't just have the formula cell * the 1/0 cell, I need to comment out the formula or it draws in too much data.
View 5 Replies
View Related
Oct 21, 2011
I am building a graph and i want to conditionally format it so that if a bar is less than 24 it wil be green and if it is greater than 24 it will be red. how to do this?
View 1 Replies
View Related
Apr 18, 2012
Column A = list of names
O1:P20 MAY contain names
If A1 = Fred
AND O5, O16, P3, P19 = Fred
Selecting A1 changes all instances of "Fred" to RED-BOLD
If A2 = Felix
AND O9 = Felix
Selecting A2 changes "Felix" to RED-BOLD
Etc...
View 4 Replies
View Related
Aug 27, 2013
I have a workbook that has multiple tabs with data already entered. I have also built a series of tables/forumlas to display summaries of the data. My desire is to be able to copy this table with all formulas/formatting/cell sizes to the various existing tabs.
View 3 Replies
View Related