Assign Value To Cell Based On Its Background Color?

May 15, 2014

I have an excel spreadsheet that 7 different categories (eg. Ave waittime, Avg loadtime, etc) that I have conditionally formatted based on 5 ratings (each rating a different color Green, light green, yellow, red, and purple) I would like to be able to give each cell a value based on what color it is eg. Green=5, light green=4 and so on.

View 2 Replies


ADVERTISEMENT

Input Cell Value Based On Background Color?

Apr 2, 2012

I'm working on a sheet that gets filled in with background colors (red, light blue, and green) and I'm trying to come up with a way to automate the process of inputting the text in the red and blue fields (red always gets PROCESS and light blue always gets SHPD) but they end up in different locations in the same columns. is there a function or macro to make this happen? I have constructed a macro to compile the sheet from my online database as it is a live system.

MODELACTIONxxx1xxx2xxx3xxx4xxx5xxx6xxx7xxx8xxx9xxx10xxx11xxx12xxx13xxx14xxx15xxx16xxx17 This is a simplified version of what I'm working with, and i'm on Excel 2000

View 2 Replies View Related

Change Cell Background Color Based On Data?

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

Background Color Of Cell Based On Colors In Column

Sep 28, 2013

I have a data base in which I classify cells in three colors (green, yellow, and red) on my own. I would like the first cell in each column to indicate if there is a red or yellow cell in it's column. Because red cells are more important then yellow cells, if there is a yellow and a red cell in the column, I would like the red to over ride the yellow and the first cell would indicate red.

So basically, if there is a red cell in the column, no matter what other colors there are, the first cell should show red. If there is only yellow or yellow and green cells, the first cell should show yellow. If there are only green cells or green cells and clear, the first cell should show clear.

View 1 Replies View Related

Change Cell Background Color Based On Multiple Conditions

Aug 6, 2008

My Excel skills are basic at best, with no knowledge of VBA other than finding the VB Editor. What I'm trying to do: Attached is the file I'm working with, to give you a better idea. When a name is selected in column B, the cells in the non corresponding columns E to N should have a black background color. For example, when you select Mike in column B, the cells in that row in columns I to N, named Dave and Dean, should become black. If you select the name Dave, columns E to H (Mike) and M to N (Dean) should become black. In addition, the background color of the non-black cells should change depending on the status.

OK : green
FAIL: red
N/A: orange
Exceptions: blue

The attached file shows a few possible results, manually entered.

View 7 Replies View Related

Changing Cell Background Color In Another Sheet Based On Cell Value

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

Modify Cell Background Color Based On Cell Content

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

Making A Cell Change Background Color Based On If Positive Or Negative Sum Total

Jul 3, 2006

I have excel sheet with colored cells.

Positive cash flow cells all have green background,
Negative cash flow cells, all have red background.

I calculate all the "sub- total" columns into one "total sum" cell. Based on this "total sum" I want the cell background to be red if the total sum is negative, or green if total sum is positive. How do I make the cell change to the proper background color based on whether it's a negative or postive total sum?

View 2 Replies View Related

Return Value Based On Background Color

Mar 11, 2014

I've got a problem to write macro. I need users to color code background (manual not conditional formatting) depending of progress in projects. Then range L6:N10 will automatically return value (from range E5:I5) based on background color.

Users got very basic excel skills and their work will limit to manual background change.

I need something like INDEX MATCH based on colorindex.

View 2 Replies View Related

Filter Rows Based On Background Color

Oct 27, 2008

how to filter rows based on the background color used in a cell? I need to make a macro that will filter/hide all rows where the cell in column B has the background color set to Yellow. Is there a way to test for that so I could get a true/false value in a column to filter by?

View 9 Replies View Related

Count Cells Based On Background Color

Aug 29, 2007

I need to be able to count the # of green cells in a range independant of what is in the cell. I can't to do it in VBA.

View 5 Replies View Related

Conditional Format Based On Background Color

Jun 5, 2008

I'm attempting to write a conditional format formula that will change the background color based on multiple color options on in a row. I have cell A:c as first, last and middle name. I utilize the rest 40+ cells that provides dates that are conditionally formatted to based on dates. I've approached this trying to reference the columns dates and cannot figure it out. Heres what I would like to do:
If any cell in from D:Z has a background color of red, yellow, green, and none it will change the background color in cells A:C. Its easy to reference one cell but not multiple. I've attached a sample of what I would like to do and I manually changed to cells A:C to show how I would like to show my results.

View 2 Replies View Related

Macro Extract Data Based On Color Background

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

Sorting Recording Based On Background Or Font Color?

Oct 17, 2006

Is it possible to sort the excel records based on background or font color?

View 2 Replies View Related

Macro Extract Data Sheet2 Based On Background Yellow Color

Apr 19, 2014

I am looking some macro code to extract data based on background color (yellow color) from sheet1 to sheet2
for detail like this below :

table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}

[Code] ........

View 5 Replies View Related

Correct Syntax For Function To Find Average Based On Background Color Of Cells In Range

May 20, 2014

I'm working on a workbook that will track staffing patterns.

The workbook has three worksheets: Sheet1 "RCS", Sheet2 "HCT' and Sheet3 "Hidden". I've attached the workbook to this thread. The password for the form is "j".

On Sheet3 "Hidden" I have two tables that are set up to collect the SUM of columns on Sheets1 "RCS" and Sheet2 "HCT". I'm finding the SUM of each range by way of the background color. I've set up the following formulas and when the "data collection tables" are in the same worksheets as the original information, the formula's work perfectly:

The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"

[Code] ........

The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"

[Code] .......

I have two more functions that aren't working due to the fact that the source values are percentages and NOT plain numbers. The above functions work great for SUM but not for percentages. EXAMPLE--Let's say, 3 sub percentages it gives me the SUM of the 3 percentages (i.e. 85% + 100% + 100% = 285% instead of giving me 95%.

[Code] ........

How might I use the following functions to find the average of the source fields instead of the SUM?

View 7 Replies View Related

Return Background Color Of Cell

Oct 6, 2006

On one worksheet I have a list of colours in cells. I want to be able to look in the list the list and return the background colour of that cell. The function i am using is:

=CELLCOLORINDEX('1.1'!$C28,FALSE)

'1.1' is the worksheet name, $C28 is the cell (coloured blue)

It seems to work because I am getting the correct background colour in the new worksheet, however it also returns the number(37) as well.

I just want the background colour to be shown only, not the color's number as well.

Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of the Font in the cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1, 1).Font.ColorIndex
Else
CellColorIndex = InRange(1, 1).Interior.ColorIndex
End If
End Function

View 9 Replies View Related

Change Background Color Of Cell When Data Has Been Changed?

Jul 10, 2012

I have 10 excel sheets that are filled with data some 20,000 each.

Some of the descriptions in that row need to be changed.

I wonder how I can create some kind of macro or formatting that will change the text color or the fill color as I make those changes automatically?

I can sort the list after that easy enough, pulling the color text or the fill color.

View 12 Replies View Related

Change Cell Background Color According To Value Found In A Table?

Feb 4, 2014

I need a macro that would change the cell background color in a calendar according to list of dates found in a table (Holidays & Vacations) which can changes on a yearly basis.

View 6 Replies View Related

Modify Code To Change Cell Background Color

Nov 11, 2008

I have the code below set to change the background color based on certain conditions. What I need to do is change it so that if none of the conditions are met, it does not change the color at all. As it is written now, if anything is entered in a cell that has a background already set, but does not meet these conditions, the cell turns white.

View 4 Replies View Related

IF(VLOOKUP) Formula That Also Copies Cell Background Color To Target Cell

Jul 4, 2014

I am currently using this Formula to Search a table on sheet 2 (Tabelle2) and copy the date in the relevent cell (Cells) into the correct cell on Sheet 1 (Master Sheet).

[Code] ..........

Used in cells E10:AZ30 and E33:AZ46

This works perfectly and is the formula behind all relevent cells in Sheet 1.

I would like to keep this but to add that it also copies the background color of the cell in Sheet 2 to the cell in sheet 1.

If i can Bring this Formula into VBA and add the color changing part, It must only work on the cells listed above..

View 9 Replies View Related

Changing Empty Cell Background Color Depending On Different Cell Value

Aug 7, 2014

I'm trying to create a tag with a color border. What I desire is to fill the BLANK cells around the tag, A1:D1 + D1:D19 + A1:D19 + A1:19 in a certain color based on the text value of the cell B11. There are 5 different values, such that if the B11 read Red Sox - the boarder is going to be red, if it reads Houston Astros it will be dark blue, etc..

I have a similar problem with changing the color of the cell based on the month. So regardless of the year, 2014, 2015, 2016, etc... If I use MONTH() function I can just get numbers from 1-12. I want Cell C16-C18 to be certain color depending the date entered in cell C17 such that for each quarter, months 1-3, 4-6, 7-9, 10-12 they are different color.

I have had no luck with conditional formatting (and I also believe that it is good up to 3 cases only). I am decent in logic/programming language but have little knowledge with macro notation and especially how to run them in excel 2013. I do know how to start it alt+F11 and that I need to make sure that code is written under the specific sheet where my tag is located.

View 6 Replies View Related

VBA Macro To Delete Entire Column When Background Cell Color Is Red

May 17, 2013

with performing the following tasks with VBA:

1). For each cell that in the range that has a Red background delete the entire column
I have attached a sample spreadsheet where I have tried to do this. It's not quite working. It does not seem to work when there are RED cells next to each other.

I know that I can do a simple thing like column("A:A"), but I'd rather have it in a loop as in the sample code, in case the columns change in the future.

2). The second task is to delete the rows in the spreadsheet where the first cell in the row is a blank.

View 4 Replies View Related

Filter / Extract Rows With Cell Background Color As Criteria

Dec 4, 2013

I have about 30 spreadsheets that I need to sort data. I'm using a Excel: Mac 2011.

Each sheet has about 100 rows and 80 columns.

I need to extract the rows that have a colored cell somewhere in the row. There are three types of colored cells: black, green, and red. The green and red have X typed in them too. The black cells have D typed in them.

Every row in the sheet has at least one cell with an X in it (colored or not).

I just need the rows with a colored cell to be extracted or filtered from all the rows without colored cells.

View 1 Replies View Related

Macro Button To Change Background Color Of Specific Cell

Sep 17, 2008

My boss wants a spreadsheet that has multiple buttons on it that will change the background color of a specific cell to four different colors. Example:

Text written in Cell B3, Button in Cell A3 that will change the Background color of Cell B3 to either Green, Yellow, Red or Blue.

There will be a lot of buttons on this worksheet following the same format as above. I don't want to change the value of what is in the cell, just the background color.

View 7 Replies View Related

Alternate The Background Color (fill Color) Of Rows In A Spreadsheet

Aug 19, 2009

how I can alternate the background color (fill color) of rows in a spreadsheet. Say I wanted every other row to be gray starting at row 10.

View 6 Replies View Related

Count By Font Color & Background Color

Dec 12, 2007

In excel 2000, I am trying to count the lines in a spreadsheet that has the first cell in each row highlighed in yellow. Additionally, I am trying to count the number of rows that has a red font used in the cell in column D.

I have reviewed other posts by searching counting highlighted cells, but I do not understand how to complete the entire process. One of the suggestion was to create a vb script in combination with using =cellindex(??). I do not really understand how to create this, name it and the run it.

View 8 Replies View Related

Changing Textbox Background Based On Other Cell Text

Jul 15, 2014

I wish for a text box (drawn Text Box, from the "Shapes" tab) to conditionally change its background color based on whether a cell in a different sheet says "Online" - in which case it should be green, or "Offline" - in which case it should be red. So far, the code that I have that doesnt work at all, which I'm not even sure where to place (I tried in the Workbook - Open?), is the following:

[Code] .....

I also need to do this for a total of 9 Text Boxes, if that changes anything.

View 6 Replies View Related

Change Row Background Color Using VBA

Nov 19, 2012

Try to find a way to automatically change the color of a row, based in changing the name in column A.

I tried conditional formatting but the name (or dates) in column A are not the same (always other and other).

I failed to adapt any VBA CODE (found in GOOGLE) because column A varies a lot.

Annex a photo and an excel example.

EXAMPLE FORUM HELP.jpg
EXAMPLE 1.xlsx

View 9 Replies View Related

How To Count Background Color

Apr 7, 2014

Is there a way to count the background color from a range of cell? for example: In a range of A1:B5 there are 5 cells highlighted with yellow background, the formula should give me 5 counts for yellow background.

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved