Excel 2007 :: Conditionally Format Cell To Have The Fill Color Of Different Cell
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
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.
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.
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.
I've a worksheet that is 3750 rows of cells from A to DT and contains only numbers with the exception of a header row. The rows are in groups, primarily 4 rows, although there are exceptions - these rows are separated by blank rows. Small example as follows.
I have been trying to conditionally format each group of rows and each column and color the cell in each column of four (or less) numbers in each group that represents the maximum number. It was easy enough to come up with the conditional formatting BUT it will take forever to do this manually. I've been told to use a macro but I know nothing of programming. Has anyone seen or heard of a macro that might already be written to perform this onerous task.
I am trying to use VBA in Excel 2007 to change bar chart series colors. I have found a few posts that link it to a cell background, but I'm struggling to find one that does the font color.
It would be great if I could change the bar chart series to match the color of the text in the A column, so that if I highlighted the value in A1 and changed the text color to orange for whatever reason, the chart updates the value of 1.2 to an orange bar (see below).
I want to be able to change a color of one cell according to color of another cell. suppose, cell A1 has text "apples" having red color. Now, if I enter text same as A1("apples") in another cell, say B1, then color of text entered in cell B1 should be red. I am using Excel 2007
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.
I wish to compare 2 sets of data in 2 cells and in the third cell i wish to both display the difference and format in a colour depending on the outcome of the difference. Is this possible? or is there another solution?
e.g cell A1 is 21 cell B1 is 26 cell C1 is the difference and if the difference is greater than say 2 then it would colour red if equal to 1 then yellow and if less than 1 as in 0 -1 etc it would colour green. I have been very successfully using conditional formatting to colour a cell that has the data inputted but cant now do it for the formula cell. The context is pupil progress but that i guess is irrelevant really.
I am trying to conditionally format rows based on the first cell in each row. I had this working in a previous worksheet and have gone through several examples based on answers in other threads but do not for the life of me know what I am doing wrong. I am using the conditional formating rules on the ribbon menu.
I have a range A3-W100 pre-formatted with generic data and formulae. Column A has no data. I want the text to change color for that row if the cell in column A is populated. The background and text are set to black to appear unpopulated.
When an item number is entered in column A3, the text color in row 3 changes to white. I can get this to work on one row but if I extend it through the range, all rows change. I want them only to change if the respective A cell is populated.
I am using Formula is =A3>"" Format is Yellow, Applies to =$A$3:$V$100 Stop if True is unchecked
What I am trying to do is create a multi-currency expense report. For example, cell B1 has a drop down list of three different currencies. Based on the currency selected in B1, I want C1 to reflect that number formatting using the proper currency.
Im sure there is an easy way to do this but I have tried using an if statement in the conditional formatting section but it does not work.
how to total the different currencies using the ifsum formula, so this is more of a presentation task, but still it has annoyed me to no end.
In the example workbook, the currency value selected in row E should effect the currency formatting in Rows F-L
i've been trying for teh last two days to figuire out a way to conditionally format the first cell in a row to turn a certain color based on the values of several independant cells in that row, as opposed to monitoring the whole row. I have tried numerous IF, AND, OR, formulas to no avail. The cell obviously also needs to ignore blank cells...Can this be done?
Basically I want the first cell to turn yellow if dates in certain cells are within 90 days of today, and turn red if within 30 days of today.......
Format the number in Column AL to '[hh]:mm' when Column C's value is ="P/T", otherwise format to 'General'
Column C's value is referenced from another sheet in the same workbook via a VLOOKUP function.
Column AL's value is based on an IF formula which goes like this: =IF($C4="F/T",SUM($AJ4)+($AK4/2),SUM($D4:$AH4))
Basically the above formula asks if Column C's value is F/T then count (because if F/T then the corresponding values in that row are whole numbers). If not, then SUM (because if P/T the corresponding values in that row are set to [hh]:mm format).
I have another sheet in the same workbook which has code (quoted below) which does something similar but I don't understand it enough to get it doing what I want for the sheet in question.
I have a large workbook with 2 rows of data I want to format (R25:R530, M25:M530). Row M is already populated, row R is new data that I am entering. When the value of a cell in row R is larger than its adjacent cell (R25:M25) in row M, I would like the font to turn red. When the value is smaller, I would like the font to turn green.
With the amount of time I've spent attempting to conditionally format, I could have gone through and formatted each cell individually by now!
I have certain cells in column ‘D’ with a ‘nine digit code’ then a ‘space’ and the word ‘sum’ e.g. ‘123456789 sum’.
What I require is the formula to enter in ‘formula is’ when you select Conditional Formatting so the cells in column ‘E’ changes to a green background if the corresponding cell in column ‘D’ has text in it and the last three characters are the word ‘sum’
I am writing a program in excel spreadsheet and I was wondering how to fill an input cell with color automatically in excel 2007.
I mean that, say I am writing an addition program, I am giving an input numbers in B1 and B2 and i am writing formula in B3 (=B1+B2). I want excel to fill B1 and B2 with yellow/any color automatlically. Only the cells with number input should be highlighted.
How to "hard code" a cell's fill color? I want to assign a cell's fill color to yellow. While setting the fill color is easily done, my problem is that as soon as I paste data into that cell, it looses its fill color. Is there a way to tell the cell to never change the fill color?
I am setting up a schedule maker for a coworker, in which I have divided two columns into time in and time out for each day of the week. My coworker wants the cell to be highlighted red if the shift is a night shift (Begins after 4), and I used conditional formatting to set up both columns independently to evaluate if the value of their content is after 4 PM. However, this means if a shift starts at noon, but ends after 4, only the time out cell will be highlighted red, not a huge issue but it isn't aesthetically pleasing.