Color Code Mistakes From Macro
Apr 19, 2009
I am trying to color code mistakes I find when running formulas in a macro. Right now I have it set up to color code mistakes red using the following code after the formula has been inserted in column E.
Range("E2:E75").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""Check"""
Selection.FormatConditions(1).Interior.ColorIndex = 3
My issue is, some of my checks have more than one variable as a wrong answer. For example, one check may include Check and Bad as possible outcomes of the formula in the cell.
the code required to allow two different variables to cause the cells to change color.
I tried a second set of the code for the same column and changed the formulas word to Bad but it did not highlight the cells that came out with Bad as the outcome of the formula.
View 9 Replies
ADVERTISEMENT
Oct 29, 2006
I have a spreadsheet that I'm using for a college football pool. We have almost 100 players, and have it set up for each player to pick up to 20 games each week. There is a sheet for each week of the season. Now, I copy each players picks from a message board and paste them under that players name. My problem is, that some people don't spell the teams correctly or abbreviate when they were told not too. This leads to them missing the points because excel sees it as a wrong entry. What I wantto accomplish is setting up some way to have excel verify the spelling is correct when the players entries are pasted in the sheet. I would also like it to auto-correct errors it finds...say a player types in Ohio St. but the winner is entered as Ohio State...This would be seen as a wrong entry for Ohio St. so I want excel to recognize it and correct it. Also simple spelling errors as well...
View 9 Replies
View Related
Dec 2, 2013
I am using Windows 7 and Excel 2010.
Is there a way to create a macro to color code a cell based on the value in a cell, and then look up a value in a table, then color code it based on where it fits into the table?
I have a table of values for about 30 projects. In column g - there is a CPI value (see bold column)
Example: Project ID
Name
Program
PMT
SI ID
AC
Milestone
TCP Level
[Code] ......
Here is the table:
I have to color code a cell, base on the CPI and how it fits into the table below. So if the current Milestone is M2 or M3 and the CPI calculated is .14 the cell would be colored RED, if the CPI number is 2.01 for M2-M3 I would want cell to be colored Turquiose. If we were at Milestone M6 and the CPI was 2.01, it would be colored blue. If the CPI was .75 at Milestone M5, it would be colored Green
LEGEND
Earned Value Limits
Milestone
RED
Yellow
Green
Turquoise
Blue
M2-M3
2.15
M4-M6
1.66
M7-M11
1.26
View 2 Replies
View Related
Jan 15, 2009
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.
View 8 Replies
View Related
Jun 18, 2008
I have a spreadsheet that i download from the net daily, which is seperated into columns of information.
I want to be able to look down a column and mark a cell in a seperate column if the cell font text is red.
For example looking down column A ... if the font text of a1 is red then mark the cell background colour of T1 red - if a2 text colour is red then mark the cell T2 red .... etc etc.
If the font colour in a1 or a2 ... etc etc is any other colour then do nothing.
I have 5 columns I wish to look down and mark in 5 seperate columns - I have tried to do this by conditional formating but don't know the fomula for checking font colour.
View 9 Replies
View Related
Feb 2, 2014
I am processing a fair few Invoices, which are being sent to me via e-mail as excel documents, very often they contain mistakes, a decent amount of mistakes. Usually the prices are wrong.
I keep track of every single entry on the invoice on my own document - Tracker, which I consider to be the superior/more correct document to the Invoice presented to me by my contractor.
Both of the documents have a reference number, which is a specific docket number, and horizontally, in the invoice, there is going to be a price for this docket. In my document, there is going to be a separate column for the total price.
Is it possible (I guess with VBA) to check for mistakes in the Invoice, but use the Tracker as a reference for this check.
Tracker has
columns
A - Name
B - Department
C - Date
D - Docket No.
C - Total price for the docket (calculation of E to Z)
E to Z - all smaller entries
Invoice has
columns
A - Date
B - Docket No.
C to E price for that docket, but it is spread, because departments are separated out, so each VAT account can be charged accordingly. I guess it is possible to do a separate column for the price, if it is easier to do a script that way.
Basically, I need to check if in the Invoice document, the price (C to E) for Docket No. (B) is the same as the price (C) for the Docket No. (D) in the Tracker.
I would like the wrong entries to be highlighted on the Invoice Document, so I can see straight away, that this needs attention.
Not always the price is wrong, sometimes the Docket No. is spelled incorrectly (Dyslexic contractor), hence the highlighting.
View 3 Replies
View Related
Nov 11, 2009
I use the code below to automatically set a black dot in cell
A11. I would like to add code which would color that dot
the same color as a manually placed checkmark in column C11 which
is symbol é.
In other words, in addition to the code printed below, I would
like to color the dot the same color as the color of the arrow
symbol é in column C11.
Is that possible? Thanks
=IF(AND(B11="ü",D11="X"),CHAR(108)," ")
View 14 Replies
View Related
Sep 22, 2009
I was wondering if there's a way to make the numbers in a column over 50 (as an example) green and numbers less than 50 red. (The numbers in this column are the sums of other cells, but I guess that doesn't really matter.)
This way when there's a lot of things going on you can see what's what without filtering or rearranging.
View 2 Replies
View Related
Jan 13, 2012
Is there a way to Print VBA code in color, like how it appears on the screen?
View 1 Replies
View Related
Jan 20, 2012
Is there VBA color to change ".Fill.ForeColor.SchemeColor =" into NO FILL? In other words, keeping the existing Foreground color prior to the macro being activated.
I'm in a chart.
View 1 Replies
View Related
Aug 21, 2007
I have a code that updates data from one file to another.....esentially, i run this code daily and it updates to a compiled daily log of all of the previous days data.
Currently, i am manually coloring the cells to visually assist in grouping what data has been uploaded for each day.
Sub UpdatePOQntyReducDB()
Dim lookupfilename As String
View 9 Replies
View Related
Nov 6, 2007
part of my code looks like this. Instead of it being if c.interior.colorindex i want it to be if top border color = 4.
How do i syntax this?
For Each c In Worksheets("Sheet1").Range("B7:DE7").Cells
If c.Interior.ColorIndex = 4 Then Range("DH7").Value = Range("DH7").Value + c.Value
Also, is there a way to specify Range("DH") and make the row be the current row, not hard coded to 7
View 9 Replies
View Related
Mar 18, 2009
I am using the following code to change colors in set of autoshapes (Thanks Colin_L and Norie) and it works fine for the first autoshape but has it has no effect on the subsequent autoshapes. I plan on using the code for about 200 autoshapes, I thought I could just copy the first code and keep adding it on for each autoshape... I am guessing that the code is stopping after the first set and not continuing?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$46" Then
'Change autoshape color to red depending upon cell value, or blank of no value is entered.
With ActiveSheet.Shapes("Rectangle 1").Fill.ForeColor
If Target.Value = "" Then
.SchemeColor = 1
ElseIf Target.Value >= 422 Then
.SchemeColor = 50
ElseIf Target.Value >= 1 Then
.SchemeColor = 10
Else
'it must be less than 1
End If
End With
End If......
View 9 Replies
View Related
Aug 12, 2009
Is it possible to have a code that says if I have a range of cells....say a1:d1 with d1 having a date in it....have that code say that if that date in d1 is greater than 30 days from today, make that whole range of cells (a1:d1) change color like to red.
View 9 Replies
View Related
Feb 1, 2014
I am looking to color code items in a list box called lbActiveItemList, is this possible? If so I would like it to color code based on the value in the 3rd column as follows:
If the value = 'Receive' Color code the item line as Black
If the value = 'Return' Color code the item line as Blue
If the value = 'Relocate' Color code the item line as Green
If the value = 'Lost' or 'Damaged' color code the item line as Red
View 3 Replies
View Related
Jan 28, 2012
Is there some code to change the fill color of all the combo boxes I have on a spreadsheet.
View 2 Replies
View Related
Sep 24, 2013
VBA code to do the following.
In F147 will go a value
Then if the value of F147 is greater than 20 the color of D144 would turn green.
View 3 Replies
View Related
Apr 25, 2007
I am trying to a macro that allows me to change the backgroud color and font color depending of the value of the cell.
For example is cell A1 is having the value between 1 to 3 the background color of the cell will the light turqoise, if the value is between 4 to 20 the cell background color will be green and so on.
I have based on certain posted example and adapt to my code but somehow it is not working.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Select Case Target.Value
Case Is = 1, 2, 3
Target.Interior.ColorIndex = 34
Case Is = 4, 5, 10, 20
Target.Interior.ColorIndex = 43
Case Is = 30, 40, 50
View 9 Replies
View Related
Jan 22, 2008
I would like to have a vba code that changes the cell color so that if there is number 1 (just number 1) in some cell the background of the cell changes in to shade of grey and cell that has number 2 changes into darker shade of grey and so on. I have numbers 1-10 in random order in my sheet. I mean there is many cells that has the same number and the sheet is quite large so vba code would be ideal choise to do it quickly.
View 9 Replies
View Related
Jan 25, 2009
I am wondering if there is any good way of coloring different sections of the source code in excel to show what different programmers have added?
View 9 Replies
View Related
Apr 29, 2009
I am trying to create a model that has a mixture of conditional formatting and data validation formulas within it, but have run into a problem in one area. Essentially, cell E10 is a Validation which pulls a drop down list of names. One of the options in this list is "Other".
I want to create a VBA code to make sure that cell E12 will get rid of any input, turn grey (color index = 15), and potentially lock (if possible!) if the "Other" is chosen by the user in cell E10. If any other value is chosen for cell E10, Cell E12 needs to remain unlocked and white (color index=2).
View 9 Replies
View Related
Mar 5, 2010
I have values that I want to color code in my table. Originally I was tagging them as numbers, but instead I need them as colors.
Function conversion(pVal As Double)
If pVal > 0.05 Then
conversion = "1"
ElseIf pVal > 0.001 Then
conversion = "2"
ElseIf pVal > 0.0001 Then
conversion = "3"
ElseIf pVal > 0.00001 Then
conversion = "4"
End If
End Function
Now I want instead of conversion = "1" etc to be
Function conversion(pVal As Double)
If pVal > 0.05 Then
(make white)
ElseIf pVal > 0.001 Then
(make brighter blue)
ElseIf pVal > 0.0001 Then
(make green)
ElseIf pVal > 0.00001 Then
(make navy blue)
End If
End Function
I want the text and cell the same color (number and cell so the number disappears so that I just have a colored cell but the value is still there if I want to look at it).
View 9 Replies
View Related
Oct 7, 2006
I must highlight cells that appear more than once.
What I mean is that if an item in a cell appears more than once I highlight it in blue, if the same happens twice I highlight that cell red etc and so one. does anyone know how to do this in excel.
View 9 Replies
View Related
Dec 11, 2006
I am trying to color code the cubes in my chart. I'd like to be able to be able to specify a specific color per cube. What code what I write for the Sheet to do this?
View 5 Replies
View Related
May 12, 2007
I searched through the files and found a macro that works for me to color code my spreadsheets in Excle. Hwoever. How can I set it to stop when Cloumn G = the word "Created"?
Sub prettyPattern2()
Dim lRow As Range
For Each lRow In Range("A6:K500").Rows
Select Case lRow.Row Mod 2
Case Is = 1: lRow.Interior.ColorIndex = 34
Case Else: lRow.Interior.ColorIndex = 35
End Select
Next lRow
End Sub
View 8 Replies
View Related
Oct 23, 2007
Well I am back to the colour coding of protected cells
I am using excell 2003
Many cells in the worksheet are locked
My problem is that when I am debugging I forget to turn sheet protection on
when I am finished And then users can overtype the cell formulae
How can I set excel up so that when the sheet is protected all the Locked cells have a green background , but when the protection on the worksheet is turned off
then the locked cells must be a red
View 4 Replies
View Related
Jan 9, 2008
Is it possible to use different text colours as part of the [Value_If_True] or [Value_If_False] when using an IF formula? For example =IF(AS3<34,"ok","not ok")
In the above formula I would like the “ok” value to be in Blue and the “not ok” value in Red.
View 2 Replies
View Related
Dec 18, 2012
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?
View 1 Replies
View Related
Jun 13, 2014
VBA code in worksheet won't revert a changed cells color back to 0(white) after removing the comment.
Observations: code does set the desired cell colors to Yellow after inserting a comment, however removing said comment, and re-activating the worksheet does NOT reset the color after deactivating/re-activating the worksheet.
[Code] ......
View 2 Replies
View Related
Jan 25, 2013
I am looking for code that would delete all columns in a worksheet that do not contain a fill color.
View 9 Replies
View Related
