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
ADVERTISEMENT
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
Jan 15, 2009
In a range of cells (A1..d8) which have background set with multiple colors. Is it possible to set another range of cells (H1..K8) whith the same backgrounds as the other range and so that if one cells background changes the cell in the second range whill also change to that new color?
View 11 Replies
View Related
Oct 21, 2008
I have an excel sheet in which many cells have a background color of Green. These cells are located all over the sheet. I want to change the background color from Green to Red not affecting the color of other cells in the sheet.
I will be realy really thankful, as I have to do this exercise for more than 50 worksheets.......If I start doing it manually by selecting each celll with green color background and changing it to red.....you can well imagine my condition.....my life would get shorten significantly while doing this.
View 6 Replies
View Related
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
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
Mar 18, 2013
I'm using this code below to change the background colour of a range of cells:
Code:
With .Range("I18:J18")
.Locked = False
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 9739376 'RGB(112,156,148)
.TintAndShade = 0
.PatternTintAndShade = 0
End With
I am receiving 'Object doesn't support this property or method' errors starting with the highlighted row. I'm unable to understand where I've erred. Note that range I18:J18 are merged.
Is this method an efficient way of simply changing a cell's background colour?
View 1 Replies
View Related
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
Apr 20, 2012
how can I change the following code so that the cell background colour remains the same. the below code unlinks the hyperlinks but deletes the backgroundcolour also of all the cells.
Code:
Private Sub Button3_Click()
ActiveSheet.Hyperlinks.Delete
Range("A7:W100").Select
[Code].....
View 1 Replies
View Related
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
Nov 19, 2008
What I need to do is have a cell that will be say yellow until there is information put into this cell. The information could be in the format of text or numbers. The information would not always be the same so it would need to be yellow when there is no information in the cell and another colour or white when there is information in the cell.
View 6 Replies
View Related
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
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
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
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
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
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
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
Sep 26, 2006
how to make a cell a color depending on a number in a different cell? Example...if A2 is 92.6 how would I make D2 blue? But if A2 were 91.9 how would I make D2 red?
View 5 Replies
View Related
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
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
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
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
Aug 8, 2012
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).
T. A1 B1
Sample 1 1.2
Sample 2 2.1
Sample 3 1.7
Sample 4 5.6
View 3 Replies
View Related
Sep 16, 2009
This may have been answered on here but can not seem to find it. My situation is I have values in A1,A2 & A3 that are like counter reading so the value is always changing. What I am looking to do is change the cell color if one of the values is over 500 from the other two values. Say A1 is 3000, A2 is 3250 and A3 is 3500. I would like the cell for A3 to change color.
View 2 Replies
View Related
Jan 15, 2014
I inherited a worksheet with 70k+ rows of data. As shown below each row contains a record number(Col A) with the date(Col B) it was created and value(Col C). There are 5 records per day. You can see in the example below that Col A has the value 30 duplicated for each day. Is it possible to rename only the second "30" in Col A for each day?
Col A
Col B
Col C
10
1/14/2014
10
[Code] .........
View 3 Replies
View Related
Jan 26, 2007
I'm having a difficult time figuring out out to coordinate two cells together.
If a "1" or "2" is entered into cell A1, I would like cell A2 to have a drop down menu show a "0". If a "3" is entered into A1, I would like B1 to have a drop down menu show "1" or "2". If a "4" is entered into A1, I would like B1 to have a drop down menu show "3" or "4". Lastly, if a "5" is entered into A1, I would like B1 to show "5" or "6".
I don't know if this is possible without using VBA.
View 9 Replies
View Related
Sep 3, 2009
I was wondering if it is possible to fill a row with a color depending on the value of a certain cell. Say, if the value of cell N5 is 0 then row 5 turns red.
View 3 Replies
View Related
Jul 3, 2009
I have another macro request.
I would like to ‘grey out’ a certain qty of cells depending on the 1st number in another cell. So 1st of all it would have to check in column B to see if there is any data and then check for the first number in that cell (there will be many numbers in the cells but we only need the 1st one (Ex.: B3 = “3 / 8 & 8”).
Then it should start counting from column D the found number of cells multiplied by 2.
Ex.: if Cell B3 contains “3 / 8 & 8” it will count 3 x 2 = 6 (3 cells x 2 = 6 cells)
Then, if after that count (now at I3), in the next cell over (J3) if the is data in the cell above (J2) then it should apply an Interior.ColorIndex of 15 for the next cells until there is no data in the cells above (if nothing after K2 the grey will be applied to cells J3 & K3 only).
View 14 Replies
View Related
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
