Color Code Cells Based On Digit Assigned To Name
Jun 21, 2007
I have a spreadsheet with a row for a company name, their offer price underneath that, and a 1,2, or 3 underneath that. In a seperate tab I have the offer prices in order of rank with the company name next to the corresponding price using the Index function. I also need to be able to color code the background of the offer price based upon the 1,2 or 3 associated with each name. I am new to the Index stuff so I need some help putting this one together.
View 3 Replies
ADVERTISEMENT
Dec 26, 2007
I have 16 columns (B:Q)
Row 1 contains either a number, the letter x, or the letter a.
Cells in Range("B2:Q628) contains either the letter x or is blank
If a cell in row 1 contains an x, then all cells in that column with an x has an Interior.ColorIndex of 45
If a cell in row 1 contains an a, then all cells in that column with a blank has an Interior.ColorIndex of 45
If a cell in row 1 contains has a number, then nothing
View 9 Replies
View Related
Nov 15, 2006
I have a list of data that is copied from another application. Once data is pasted into Excel it is one cell. My challenge is following. I need to extract only the rows that appear below particular 9 characther alfa numeric code which is the first character in a row. This character however only appears sporadically so there is a whole list of data underneath it. See below....
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
Apr 25, 2014
I have an address (city, state, zip format) in a cell. Some of the zip codes only have a 4-digit zip code which is the way the file was received. I know these zip codes have leading zero. How can I convert these to a five-digit code. Examples are as follows:
Waltham MA 2453
convert to 'Waltham MA 02453'
Boston MA 2210
convert to 'Boston MA 02210'
CEDAR GROVE, NJ 7009-1174
convert to 'CEDAR GROVE, NJ 07009-1174'
TEANECK CITY, NJ 7666
convert to 'TEANECK CITY, NJ 07666'
View 3 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
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 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
Mar 13, 2014
I have made several Shapes and assigned macros to them, all is good.
What i am curious about: is it possible to highlight or change colour of the shape on press and return to normal if another is shape is pressed? Just anything to make that particular shape stand out that it has been selected then return to normal when another shape has been selected...
[Code] ....
That is the code so far that is attached to 1x shape...
View 5 Replies
View Related
Mar 6, 2008
I'd like to write a macro to create buttons with the caption "Fix" in over 300 cells. Furthermore, I would like each button, when pressed to run a macro that would copy and paste the values (paste special) of the entire row in which the button is situated as well as copy and "paste special" the values in the fixed cells $J$2, $K$2,$L$2, and $M$2. I've attached a file to clarify what I'm sure is an extremely convaluted statement of my problem.
View 5 Replies
View Related
Jun 10, 2014
I have a little trouble with my macros. I wrote several different macros, each for a special purpose. Then I created some Buttons, which would make it easy for others to work with them.
But I have now experienced several times, that my buttons don't keep their assigned macro.
View 5 Replies
View Related
Jan 15, 2014
I have cells in range L12:BN1000 with formulas that will output a 1 or a 2.
If the output is 1, I would like the cell to color yellow
if the output is 2, I would like the cell to color orange
the reason I dont want to use conditional formatting is because the use needs to be able to copy and insert rows and by doing so the use would need to manually add the new cells into the conditional format range. I would like to come up with a macro that applies this condition to a large range.
also, were do I add this macro? under sheet1? this workbook? or as a module?
View 2 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
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
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
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 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
Aug 1, 2014
I am using the following formula to calculate due dates based on the rank i assign an issue.
Note: a similar formula is used for the due date of section one, but if it is not complete, i do not want this formula to kick in a determine the due date for section 2. The formula itself is working fine, but is giving a 'due date' of 1/30/1900. I was wondering if there is a way to not display anything if it has no number to calculate from.
=IF(ISNUMBER(SEARCH("S",D11)),F11+30,IF(ISNUMBER(SEARCH("B",D11)),F11+30,
IF(ISNUMBER(SEARCH("A",D11)),F11+30,IF(ISNUMBER(SEARCH("QNM",D11)),F11+30,""))))
View 4 Replies
View Related
Jul 31, 2009
I am using Excel 2007.
I have a spreadsheet with about 30 items in 30 rows. I have a picture object which corresponds to the item # and other info in each row. When I sort the rows, the picture objects don't get sorted with the rows and end up no longer corresponding with the item # and other info.
I have right clicked and set the properties for each picture object to "move and size with cells". This does not prevent the problem.
View 9 Replies
View Related
Apr 14, 2013
I have this code:
Code:
If .Range("K" & t) = "AUTO" Then
.Range("K" & t) = Format(Application.VLookup(.Range("A" & t), rcore, 54, False), "h:mmA/P")
With .Range("K" & t)[code]....
It is intended to change the font colour to white when the cell = "AUTO". It does, but it also changes the contents of the cell (like NA, and NR) to white when they aren't equal to AUTO.
View 2 Replies
View Related
Apr 2, 2008
I’m trying to make my life a bit easier, by adding a few macros and formulas to the spreadsheet (Everything was done completely manually before I got here!!!).
What I would like to do is take two columns, which contain a start and end time for work shifts, and colour them GREEN once I have entered a name in the Worker column (Along side the two with the time), and also to fill a cell with a Yes or a No. I’m aware of auto conditioning, and I’ve tried to have a play to get this to work, but I just can’t work it out.
I have posted a link to an image which shows what I want. I hope I've explained it well enough!
http://img530.imageshack.us/img530/6239/excelspfk0.jpg
View 9 Replies
View Related
Sep 3, 2004
I've created a Gantt chart using the tip #58. Now how can I count all the cells that are colored?
View 9 Replies
View Related
Dec 8, 2009
I have many dates in a column in a 6 digit format such as:
950223
950224
950225
These dates are Feb 23, 1995; Feb 24, 1995 etc.
How can I convert these dates in the column to 2/23/1995, 2/24/1995 etc?
View 3 Replies
View Related
Jan 27, 2005
how can I automatically fill cells with certain colors based on the value of the cell. (i.e. I want to search an entire workbook and fill cells with values between 80 and 99 green, 60 to 79 yellow and 0 to 59 red.)
View 9 Replies
View Related
Jan 15, 2008
I’m trying to change color in cell B9 according to different conditions in Cell C9 (than on b10 according to c10 and so long until necessary - probably will be around 2000 lines). Since there are more than 3 conditions and in two cases condition depends on the color of the C Column, I can not use conditional formatting. I’ve searched and found similar forums here, but since I’m ignorant in VBA code, I couldn’t manage to make adjustments. So if you can help me with the code, I would really appreciate it!
Condition and Results required would be:
IF column C = “S” than on Column B = color cell light blue with white border
IF column C = “P” than on Column B = color cell Green with white border
IF column C = “A” than on Column B = color cell Yellow with white border
IF column C = “L” than on Column B = color cell Red with white border
IF column C = “C” than on Column B = color cell Dark Blue with white border
IF column C = “ ” than on Column B = color cell white
IF column C = “V” and has white background than on Column B = color cell white
IF column C = “V” and has a Dark Grey background than on Column B = color cell Dark Grey
I don’t know if the last two are feasible.
View 6 Replies
View Related
Mar 19, 2014
How would I fix my formulas from not working when I filter other columns? It is a simple =A1/A3 cell and this calculation is down the full column. When I filter any column, the calculation does not stay with the assigned cells.
View 2 Replies
View Related
May 6, 2013
I have a table where
Then I have a set of codes that look like 1ZGM.BA, where the first number/letter relates to a chart
The chart contains approx. 34 different codes. numbers 1-8 and letters A-Z.
Codes look like 1ZGM.BA or A1QL.AA ...I'm only interested in the first character (does not matter if its a letter or number) to decode the product.
The chart looks like
1=Widget Smooth
2=Widget Rough
...and so on.
A=Widget Thick
B=Widget Thin
...and so on to letter Z.
I want to decode the first letter/number of a code that looks like 1ZGM.BA, where the 1 would mean the product is a smooth Widget, and A1QL.AA would equal Widget Thick product.
View 1 Replies
View Related
Jun 25, 2007
I have a long standing problem, i need to use some code that will search a range of cells , say j1 to j15, for a text "ship", gather the value from the equivalent h column cell and then use that value in futher code to colour some cells. So if the value returned is say 5, then colour the next 5 columns from column A whenever "ship" is typed.
View 9 Replies
View Related
May 29, 2008
I have many cells in a column, where I would like to change the colour depending on the value in the cell above. If one cell is more than or equals 600 000 and the cell below is less than 600 000, then the lower cell should change colour to red.
This should be repeted for all cells in the column, until the last empty cell.
Sub macro()
x = 7
Do While Cells(x, 4).Value <> ""
If Cells(x, 4).Value >= 600000 _
And Cells([x + 1], 4).Value < 600000 Then _
Cells([x + 1], 4).Select _
Selection.Font.ColorIndex = 3
End If
x = x + 1
Loop
End Sub
For this code I receive the error code "Compile error: End if without block if".
View 7 Replies
View Related
Jun 3, 2008
I have a 2 lists of manually entered values where the values in each row are related. They are related in the sense that if the difference between them is equal to or less than, a given amount then the cell containing one value is colored Blue. e.g.
COl A holds Value x while COl B holds value y. I need to check which value is entered in columns A and B and if a given difference or greater exists colour the cell in COl B. For example the relationship relevant to a COL A value of 21.0 is 11.0.
So, I enter the following;
A1 B1
21.0 13.0 <----------------No color Blue
I enter new values
21.0 9.5 <----------------B1 is blue.........................
View 2 Replies
View Related
