I have a work sheet with over 500 different name enteries. I need to change the cell color to red if the cell has the name "john" in it. The names are listed in a column (ex. A1:A500)
The cell could have the name "John Jackson", "John Johnson", "John A", or just "John". As long as the name "John" is found, the cell color should turn red.
If the cell has the name "Mike", "Mike A", "Mike Johnson" then change color to Green. Here I am looking for any cell that has the name Mike in it.
and so on ...
I have over 500 different names so Conditional formating won't help me here. I was wondering if I could do this with VBA coding.
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.
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.
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.
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?
I think looking at the attachment might make more sense, but here is a brief description of what I want to do. I am trying to change the colour of a cell based on looking up the value contained in that cell in a predefined list (which in my example I have called a legend). I need to do this in VBa but my VBA is not at all good.
I have created a gradebook template so that I can keep track of my assignments as I get them and keep on top of my grades. Any way that I can code excel to work so that if my current average in the class ever drops below 70%, I want the color of the text to change to red to alert me that the grade is too low and needs to come up. I'm new to using VBA in excel and not sure how to do it or what to code.
I am using Excel 2010 and trying to change/edit the color of the tabs in my workbook to turn green or red based on a y or n placed in a cell (the same cell on each tab). I have tried variations on several themes others have asked about as well for Excel 2007 and attempted to adapt them to fit my situation but none seem to work. Here's what I was starting with:
Private Sub Worksheet_Change(ByVal Target As Range) Select Case Range("A1").Value Case Is < 2.5 Sheet1.Tab.Color = vbRed Case Is > 2. 5, Is < 4 Sheet1.Tab.Color = vbGreen End Select End Sub
The cell I'm using is F2 and my cell value is simply y or n. I realize the example above references numeric values and greater than/less than options, but I'm not sure how to correct this for my need.
i have had to change the quarters that our company works in and in doing so have to make some changes to my VBA code. our quarters have moved back 1 month so Q1 = Dec/Jan Feb now.
below is a copy of the code that used to make the associated cell font color red depending on the current date. i need to change it to be relevant to the new quarter structure.
[Code] .....
As you can see if the month value is less than 4 (jan/feb/march) it will highlight red....however i dont know how to change the range to specifiy the ranges 12,1,2 as per the new quarter structure.
I'm trying to create a simplified Gantt chart of sorts, and cannot figure out one piece of it. I'm not sure how to write out exactly what I need, but here goes.
I would like to have a formula that looks at the percent completed (which the user inputs) and multiplies it by the total duration for the task to give a total number of days completed. Then, under the corresponding dates, the color of the cell would change. I've attached a simplified version of what I'm talking about.
On the attached spreadsheet, the total duration (D2) is 5 days and the percent completed (C2) is 40%. This calculates to 2 days. With that said, I would like the cells under the first 2 days under the listed dates (E2 and F2) to change color. When the percentage complete reaches 60%, then G2 would change color, when it reaches 80%, then H2 would change, and when it reaches 100%, then I2 would change.
My guess is that the formula will need to result in a particular value, and then I'll use Conditional Formatting to do the actual changing of the cell color. Assuming this is correct, I still don't know what formula to use to accomplish this.
I have a spreadsheet that uses VB macros to calculate sums of cells based on the font color of the numbers inside. It used to be fairly easy going through each cell and "classifying" them by color, so that my macros can go ahead and sum the numbers in each respective color's cell... but now I have a huge amount of numbers and would like to automate the process somewhat. Here's an example using the A and B columns:
flight $400 hotel $150 hotel $130 meal $20 meal $15 flight $350
I tried using conditional formatting to automatically change the color of the adjacent cells based on the presence of a keyword such as "flight" or "hotel", but this change is only cosmetic, and doesn't actually change the font color (it is still the default black, hence why my color-summing macros won't work!).
I'm including a sample macro for what I use to color-sum my cells, but what I am looking to automate the color-coding process based on looking for keywords as explained above in my example. Here is one of the working color-summing macros (for red, in this case) if you'd like to use it as a reference:
Function SumRed(SelectedCells As Range) ' Adds the values of the cells where the font colour is red(3). Dim Cell As Object Dim x As Double
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 would like to accomplish 2 things in my Excel 2010 spreadsheet by click a cell which already has a number and formatting in it.
1. How do I change the color of the cell, the color of the number in it, and the border around it. (Make it look like I just pressed a button by clicking it.)
2. At the same time have the text in different cell and the number in the selected cell appear in another different cell. (Text in a different cell = A , and the number in the selected cell = 23, the value in the resulting cell be "A 23") Everything I would like to happen at the same time by clicking the selected cell. I would also like this to be done several times by clicking different cells and not changing the previously selected cells.
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.
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
I wrote a macro to color the cell values in the rows based on their average value. For eg if the cell value is less than 0.2 Avg, they should be red color,if value is between 0.2 and 0.5 it should be yellow. This part is working fine
Now based on the color of the rows cells , need to write a macro for the header one. Logic is Coloum header should be in red colour, if in one or more number of rows cells are red. same with yellow ones. Could you please help me out in solving this with logic.
I'm trying to change a cell color depending on another cells color. This is what I have so far:
PHP Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 5 Then Customer = findit(Selection.Value, Range("L:L")) Colour = Range(Customer).Interior.ColorIndex If Colour = 3 Then With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With End If End IfEnd Sub
findit is another function I found on the internet:
PHP Function findit(v As Variant, r As Range) As String findit = "" For Each rr In r If rr.Value = v Then findit = rr.Address Exit Function End If NextEnd Function
Everything works, except it just wont change the cell color. I have tried loads of different ways, Target.Interior.ColorIndex Selection. ActiveCell. Range().
If I put a msgbox in the If section it shows up so everything is working, besides the color.
EDIT: im using this with data validation list if that makes a difference.
I am trying to make a reference to tell me when my rigging gear is due for inspection. I want the cell to change colors when a date is in range. For example i would like a red cell when the item is due or past due for inspection. A yellow cell for when the item is 90 days out from the due date. i have used conditional formating and used =E$-30<=TODAY() and changed cell to red. but when i add another format, for E$-90<=TODAY() for yellow, it will not work.
Once this is solved, i will need to make the entire row change color.
what I want can be done in the 'Conditional Formatting' but I can't seem to figure it out.
I have a spreadsheet with specific dates down the left colum and I need that row to highlight when the date comes, and then return back to normal when the next date comes around and gets highlighted.
so for example, A1 = 1/1/2008 I need that row highlighted when 1/1/2008 comes around, then the next date would be A2 = 1/15/2008 and so on..
I was copying cells from one spreadsheet to another using the following IF statement. It basically says that if the cell in this spreadsheet is blank, then put nothing in there. If it is not blank, put in data from another spreadsheet. The problem is that when it copied the information over, it was in a completely different color than the original cell. So, for example, if I put the following formula in the new spreadsheet, it will copy the cell information from the listed spreadsheet in this formula and place it in the appropriate cell, but it is changing the data from its original color to green.
=IF($I2="","",'[Reinstatement Template--KGM.xls]Car Care Non CA'!L1)
I set up a range of cells to show a form when they are double clicked. This form gives the user the choice to pick a color and text from a list. When the user clicks "OK" the form closes and changes the cell based on the user's input (i.e. Red, "N/A"). I want only the double cliked cell to change color and text.
Problem: My code runs fine, but I cannot find a way to let the form change the text of the cell. Only the color is changing. I have tried calling the cell out in the code, but I get an error. I cannot find a way to change the text in the cell.
Code: Private Sub CommandButton2_Click() Dim ColorChosen As String Dim WordChosen As String[code].....
I'm trying to create a simple VBA function in Excel so that I can sort and calculate results based on various conditions. I can get everything to work except changing the color of the cell where the output is placed. Here is what I have so far:
Function nameOfMyFunction(arguments) If conditions Then nameOfMyFunction = "No GRP" ActiveCell.Font.Color = RGB(255,0,0) End If
I'm sure that its something simple, but I've spent long enough on my own trying to figure it out and nothing seems to work.
"I want to make a spread sheet where the "shading" changes based on the text entered into the cell. ie I want to be able to enter yellow into a cell and have the cell shading change to yellow."
I have a worksheet with 3 particular columns (L,M,N) that have drop down menus. The menu is populated with items that are referenced on a different workbook (different file). Because there are multiple worksheets accessing the referenced list, this is a master. When I change the master, the lists update, but the cell text does not. Example:
If I chose apple from the list, but later in the master I decide I want apple to be banana, while the drop dowm box will now have banana instead of apple, my cell still says apple.
I don't necessarily want apple to change to banana, but I want a visual cue that this is incorrect. I wanted the cell text to change to red if it is not equal to any of the list items on the master file. I want this to be passive, i.e., I want it to change automatically, so I don't have to press a button or some other activation.
