Cell Color Based On Data Within
Jun 26, 2008
To my problem: I have created a spreadsheet from a questionnaire I gave to al members of staff, i have the names on the left and questions going along the top (just so you have an idea of what it looks like). To make it easy to spot problem areas I have coloured cells according to the answer (there was a choice of four). Now this works great from that point of view, but as they are just coloured cell with no data i cannot create a chart displaying the data. I understand I could just put a 1 or 2... ect in the cells as well and colour them the same as the cell so they are not visible (as it would look untidy if they were).
I was wondering if there was a way to use the colour of a cell as data?
If not is there a simple bit of VB that could tell the cell and text to change colour depending on data (e.g. If i put "1" in a cell, it will change the cell colour and font colour to red)?
View 9 Replies
ADVERTISEMENT
Jan 30, 2014
I have an Excel 2010 spreadsheet consisting of many worksheets (20 or so). Each of these worksheets contain detail level data regarding different projects. One of the columns in these worksheets is the 'Status' column (column F). There is conditional formatting on this column where if the text is 'G' then change background to a green color, 'Y'=yellow, 'R'=Red and 'U'=Grey.
The first worksheet is a summary sheet that I would like to pull information from each of the detail worksheet's columns B, D, E, G and H if the status column (Column F) is 'R' or 'Y'.
The number of rows in the detail worksheet can change each week (as few as 0 and as many as 100)
View 2 Replies
View Related
Nov 17, 2009
I want to change the color of a cell's background based on valid data in the cell. I have a pull-down list, it uses a named list, and this all works fine. I want the cell to be red, and then after the user selects a valid entry from the pull-down list, I want to change the cell to yellow.
View 2 Replies
View Related
Mar 29, 2012
I have a worksheet set up where one column of data (we shall call it "A") is the result of several other columns' calculations (uses a formula referencing other cells). I have an entirely separate cell that also gets its data from other cells (we shall call this "B"). What I'm trying to do is make the shading of column "A" dependent upon data from column "B". For example, if column "B's" value is >24, I want column "A" to shade in red.
View 10 Replies
View Related
Oct 27, 2009
I have attached the relevant spreadsheet for which I need to alter the color of the columns based on Site number ( Sheet 1). % Mortality will be represented in the Y-Axis, and the Site numbers would be on the X-Axis. All columns (% Mortality) except one will be of the same color, and the one of a different color will indicate a specific site. As an example, site 86 is colored differently. The way I require the chart to look is shown on Sheet 1.
After reading through some great posts on Ozgrid, I managed to do this using conditional formatting (Sheet 2), but that sort of falls short because I am required to add a data table to the chart, and the parameter that is indicated by the column bars happens to appear twice in the data table.
I was wondering if this can be automated maybe using VBA, but with the possibility of simply matching the color of columns with the font color of respective entry in the data series.
View 9 Replies
View Related
Aug 9, 2013
Summary of performance of various products against target is as follows,
Product vs Target
Color Code
Result
CH4OH
Green
1.0
[Code] ........
I need the final result automated as follows,
If 2 green of the 4 products, then final result Gree
If 2 Amber of the 4 products, then final result amber
If 2 Red of the 4 products, final result Red
Is there a way to automate this?
View 8 Replies
View Related
Apr 11, 2008
I have got a userform with lots of controls,
One of the action's on a large group of the controls is the same but except for one number
here is an example
If TextBox107.ForeColor = 255 Then ActiveCell. Offset(0, 53).Font.ColorIndex = 3
If TextBox108.ForeColor = 255 Then ActiveCell.Offset(0, 54).Font.ColorIndex = 3
If TextBox109.ForeColor = 255 Then ActiveCell.Offset(0, 55).Font.ColorIndex = 3
This makes a cell that correlates to the textbox red if the text in the textbox is red.
Now, I loads of these textboxes that all need to run the same code with just the Offset value one digit higher than the last and I was hoping I could create a loop to avoid a huge block of code but I can't work out how to make a constant that will +1 with each loop.
Also, can I assume that a loop will start with the control with the lowest number i.e. Textbox1 and then work its way through the rest of them in order?
View 3 Replies
View Related
Jan 7, 2013
I am trying to find a way to write a Macro that will auto color multiple cells based on what data is displayed in one cell. The cell I want to reference is a vlookup cell.
Basically this is a part label. And depending what part is selected from the list my vlookup will display its position on a vehicle(i.e.. FR, FL, RR, RL, Etc..). So if vlookup comes back with FR I want the various cells on the label to be orange, etc..
ALSO: if there is a way to embed it so it does this automatically (rather than run the macro each time).
View 7 Replies
View Related
Nov 1, 2009
This is probably elementary, but I'm struggling and would appreciate any help as I have very little excel VBA experience to draw from.
I have assembled code which changes the cell color based on a value change in Column A. Column A will contain many different groups of repeating values. This code works well and and I have been able to figure out how to limit the number of colors to only 2. The end result is each set of similar values in column A is visually grouped by one of two alternating colors.
The number rows in the data set is variable as the data set is extracted from SAP. The number of columns is fixed.
What I want to do now is set the cell color in columns B through F the same color that was assigned to the row in column A. So if cell A3 is set to colorindex = 6, then I want to set the range of cells B3 to E3 to the same color.
Here is the code I am using to set the color of the cells in Column A:
View 7 Replies
View Related
Apr 18, 2008
I have two columns. The first one (A) contains cells that have different Fill colors. The second column (B) contains text adjacent to the colored cells. I am trying to change the color of the text in the second column (B) to the corresponding color in the adjacent cell in the first column (A). I don't think conditional formating works well in this situation. I believe the solution would be some sort of macro.
View 3 Replies
View Related
Oct 17, 2006
I have various row cells in column (F) filled with the color Green. And corresponding text in Column G. How can I change the text of that particular row to white.
i.e.: if any cell in column F is Green, change the text color of that row in Column G to white?
View 5 Replies
View Related
Nov 18, 2007
I am looking for a very simple script that will achieve the following:
On the clicking of a button, Select and shade in a cell yellow, delete the yellow shading of the previous cell. The shading & selection should move up a column of cells, 1 at a time, in the following order:
From B10 to B9, then B9 to B8, B8 to B7 etc until the selection and shading is at B2. Once it is at B2 subsequent clicks will simply keep it at B2 (the top). Thus after 8 clicks the shading & selection should travel from B10 to B2, with only 1 cell being shaded yellow and selected at any one time.
View 5 Replies
View Related
Jun 22, 2007
if it actually possible to colour data points depending on the colour of the cell of the data it refers to? if not is it possible to colour it any other way
i have uneven amounts of data for weach month but still want the months to be displayed, this is the best way i can think of of doing this
View 4 Replies
View Related
Jun 9, 2012
I have a SS where I'm trying to copy some data based on the cell color. See Below
Untitled.jpg
For Row 1 I would like to copy all cells where the content/font color is black and paste into K6. As part of the paste I'm also trying to remove the blanks so the end result would be
Untitled1.jpg
I would then like to be able to do the same for the following rows. Not sure if there is an easy way to do this but thought its worth asking.
View 14 Replies
View Related
Oct 9, 2013
I have two columns. In column B is the date of "last check". I column A is the date of "next check". I would like to have cell A2 in yellow color 334 days after the date entered in cell A3 and than in red color 365 days after the date entered in cell A3. Same thing for cell B2 related to date entered in cell B3. Yellow color in cells announces that check will expire within 30 days and red color that check has been expired.
View 1 Replies
View Related
May 15, 2013
I have one column of data which I need to split into two. Those values that are bold need to migrate to column B. Have some difficulty in phrasing the question properly.
Original:
A
10
0
0
2
4
Desired output:
A B
0 10
2 0
0 0
2 0
0 4
* note a few formatting issues here:
1. instead of bold and not bold, they are in green or in purple
2. 0 stands for an empty cell in my data set (I tried to fill the empty cells with zeros, but they somehow inherit the colors, i.e. some zeros are green and some zeros are purple; guess that wouldn't be a problem since the desired output they'll all be zeros anyway?)
View 5 Replies
View Related
Apr 20, 2014
i am looking macro code to extract data from sheet1 to sheet2 & sheet3, with criteria background color :
1) extract data from sheet1 to sheet2 for data contains/highlighted yellow color background
2) extract data from sheet1 to sheet3 for data contains/highlighted red color background
For detail information, see attached workbook : extract after coloring.xlsx
View 3 Replies
View Related
Jun 13, 2013
I am working on a workbook with 200+ sheets and an index linking to each of the sheets. On the sheets there is a cell containing the results of calculations and I need the cell referencing the sheet in the index to change background colors depending if the calculations value is greater or less than 0. I currently have working code that also changes the tab color of each sheet depending on the value, but i am trying to add the index cell background change functionality into that. also, the index listings is subject to change. my working code is
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("d34").Value < 0 Then
Me.Tab.ColorIndex = 3
ElseIf Range("D34").Value > 0 Then
Me.Tab.ColorIndex = 4
Else
[Code]...
and the code i am trying to get working is
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngX As Range
Set myRange = Worksheets("Index").Range("A1:A500").Find(Range("C2").Text, lookat:=xlPart)
' the C2 is the reference the title that would be on the index
If Range("d40").Value < 0 Then
[Code]....
I keep getting the runtime error '1004: application-defined error or object-defined error the thing is, i ran the cell background changing code in a normal macro before integrating it in my other worksheet code first and it worked fine. I have a little code experience, though not much in vba and I am unsure what the issue is. if there is another better way to accomplish what I'm tring to do, that would also be fine.
View 4 Replies
View Related
Jul 1, 2014
I am working on developing QR Codes using some MATLAB code and it would be really convenient if I could create an excel program which changed the background color of a cell containing a 1 to black and a cell containing a 0 to white.
View 2 Replies
View Related
Jan 12, 2009
I did a search on this site and found some code I was looking for (see link:
http://www.excelforum.com/excel-prog...e-in-cell.html - Leith Ross's response code).
The code works perfectly, however, if I save the workbook with a different spreadsheet on top than the spreadsheet referred to in this code, I get an error message: Method 'Range' of object '_Worksheet' failed.
I should state that I did change "Private Sub Workbook_Open()" to "Private Sub Auto_Open()".
I assume I need to modify the code but am unsure as to how.
View 11 Replies
View Related
Dec 23, 2013
how do I turn a row range a different color based on an answer in a cell value for example:
If B4 = Wont have
Turn grey the range C4:G4
View 3 Replies
View Related
Jul 20, 2009
I'm putting the sum of 3 cells in a 4th cell(D2). If the sum is greater, I would like the sum to be the color green. If the sum is less, I would like the sum to be the color red. Is this possible?
(D2) has change value, I want to compare the new value against the existing one.
View 9 Replies
View Related
Dec 30, 2009
Quick question, I know how to change a cells background / text color based on it's own value using conditional formatting but what would be the formula to change cell A1's background color based on the value of cell C1?
View 6 Replies
View Related
Nov 7, 2007
I have a worksheet 'stock levels' which I list all my products and the amount we have in stock.
I have another worksheet called 'on order' which lists products that we currently have on order.
In the 'stock levels worksheet, I have a column called product id' & 'on order'....
I would like to do a check of the 'on order' worksheet to see if 'product id' exists in the 'product id column' - if so then color the 'on order column green, if not leave same.
View 11 Replies
View Related
Dec 20, 2011
I'm trying to set up a spreadsheet to track 20 vehicles mileage, and when they are due for an oil change. I have the value of when they are next due in column D, and the actual mileage in column G. I want to have the row for each vehicle turn yellow when they are within 1,000 miles of needing an oil change, and red when they are due/overdue.
View 2 Replies
View Related
Mar 29, 2012
I have been trying to figure out how to change the color of a specific tab based on information being entered in cell "B9" for each sheet that the tabs represent. If there is information in cell B9 on "tab 1", I want only tab 1 to change a color. If there is no information entered in cell B9 of tab 2, then I want tab 2 to stay with its default color.
There are 34 tabs I want this to happen to, out of just more than 60 total tabs on the worksheet.
View 9 Replies
View Related
Sep 5, 2013
A brief rundown on what I am trying to create. My family and I have a NFL football game we do every year to see who can correctly guess the most winners over the course of the season and then the ultimate winner receives a prize at the end of the season. After years of doing this manually, I decided to try and put my Excel skills to use and create a more "automated" worksheet.
After some heavy research, I was able to successfully create a user defined VBA which would calculate a "win" every time a cell is highlighted (in my case the winning team's name). For example, I highlight New England Patriots and when the formula calculates each person receives 1 in their win column.
However, I have now reached a problem I failed to think of. The formula does not take into consideration the picks everyone is going to make. Not everyone is going to select the winning team, so the current formula I have is giving credit for every highlight. Is there a way to add an if statement to this? For example, if person A selected New England Patriots and it's highlighted then give 1, if not give 0?
I know it's usually hard to base a formula off a cell color.
For some reason, I cannot attach the spreadsheet I am currently using. For a brief overview, cell M1 contains the color which I would use to highlight the cells in the range A2:H17. This formula is in cells B21 - B27.
The VBA which I input was:
Function CountCcolor(range_data As range, criteria As range) As Long Dim datax As range Dim xcolor As Longxcolor = criteria.Interior.ColorIndexFor Each datax In range_data If datax.Interior.ColorIndex = xcolor Then CountCcolor = CountCcolor + 1 End IfNext dataxEnd Function
Where the picks which are made should be logged. Whether each person gets a separate color and the VBA is based on that, or like I alluded to above if there is an X here and it's highlighted or something.
View 1 Replies
View Related
Sep 9, 2008
I'm trying to change the tab color based on a cell value. This I can do, but only using a Change Event with code in the Worksheet section. I would like it to update more instantly and without the need for a Change on the sheet to occur. So I was thinking a Worksheet_Activate event, or perhaps a Workbook_Activate event, but i can't get it to work.
For further info, if not confusing, the cell referenced in code below (on the Dan sheet) is filled using a user entered variable when the workbook is opened. Depending on this value, i would like the tabs to update their color accordingly and instantly, as well as the color updating if the cell B3 on the sheet is changed. Perhaps I need a Worksheet Activate & Deactivate and Workbook Activate and Deactivate?
Public sPeriod As Long
Private Sub Worksheet_Change(ByVal Target As Range)
sPeriod = Worksheets("Dan").Cells(2, 4).Value
If Target.Count > 1 Then Exit Sub
If Target.Address(False, False) = "B3" Then
Select Case UCase(Target.Value)
Case sPeriod - 1: ActiveSheet.Tab.Color = RGB(0, 0, 255)
Case Else: ActiveSheet.Tab.Color = RGB(255, 0, 0)
End Select
End If
End Sub
View 9 Replies
View Related
May 12, 2007
I have just installed Excel 2007. Is there a way to sum a range of cells based on cell color in Excel 2007 without creating a macro or UDF?
I was told there was, but I can't find it and for some my reason my help index is corrupt.
View 3 Replies
View Related
Jun 1, 2008
I have an excel sheet in which i have a cell A11 with drop down list values=YES/NO.
Now based on the value in this cell i want to fill color in the cells(B1 to B10) i.e for e.g. if i select YES in the cell A11 then the cells(B1 TO B10) should become green in color.
Attached is an sample of what i want.
View 8 Replies
View Related
