Macro That Will Auto Color Multiple Cells Based On Data Displayed In One Cell
Jan 7, 2013
I am trying to find a way to write a Macro that will auto color multiple cells based on what data is displayed in one cell. The cell I want to reference is a vlookup cell.
Basically this is a part label. And depending what part is selected from the list my vlookup will display its position on a vehicle(i.e.. FR, FL, RR, RL, Etc..). So if vlookup comes back with FR I want the various cells on the label to be orange, etc..
ALSO: if there is a way to embed it so it does this automatically (rather than run the macro each time).
View 7 Replies
ADVERTISEMENT
Apr 13, 2009
I have a macro code for conditional formatting. The first 2 lines of the macro are
Private Sub Worksheet_Change(ByVal Target As Range) ....
View 9 Replies
View Related
Mar 29, 2012
I have a worksheet set up where one column of data (we shall call it "A") is the result of several other columns' calculations (uses a formula referencing other cells). I have an entirely separate cell that also gets its data from other cells (we shall call this "B"). What I'm trying to do is make the shading of column "A" dependent upon data from column "B". For example, if column "B's" value is >24, I want column "A" to shade in red.
View 10 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
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
Aug 3, 2009
the post 5 for the actual issue. This being my first post could not update it correctly. I have put my views int he 5th post which will be more clear.
View 9 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 4, 2008
Not sure if this is possible or if I would need to run a VB macro. image multiple columns with text headers.
'A'=Project Number 'B'= Date 'C'=Description etc etc etc 'S'=Cancelled (i actually use up to 'Z' on the columns for various other data)
I have added a Conditional Format on the cells in column 'S' that if the cell data = Y then colour the cell RED. This at first glance will show that the project has been cancelled. What I would like it to do is to colour the complete row (shaded grey for example) this would then show up more easily with the amount of data that is on my sheet.
View 2 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
Oct 11, 2011
I'm currently working on a test matrix that has high level and mid level test cases. The High level results cell is tracking all mid level test cases below it. If all mid level test cases are displayed as "Pass" then the High level test cases displays "PASS". If any of the mid level test cases is not displayed as "PASS" then the High level test case result is displayed as "FAIL" However, "PASS" and "FAIL" do not accurately represent the status of the test cases in the instance of them not being filled out. Mid Level test cases start off in the "Select Result" state which basically says it that test has not been performed. I would like to have a third value displayed as "NOT COMPLETED" or "IN PROGRESS". Below is the current formula that I am using.
=IF(AND(C50="Pass",C51="Pass", C52="Pass", C53="Pass", C54="Pass", C55="Pass", C56="Pass"),"PASS","FAIL")
On thing to keep in mind that may or may not make a difference is that one of the High level test cases has 30 mid level test cases and from my understanding, IF / AND function only allows up to 30 logic inputs (not sure if that is the correct terminology or correct at all).
View 8 Replies
View Related
Jul 26, 2014
When the sum of B7-D7 is less than cell (I7) I would like to change the shade of B7-D7 to orange to indicate that larger values need to be entered to equal the value of cell I7. And when the sum of these three cells does equal I7 their color should change to green. I recorded two macros to change the colors and I've run them to verify they work. But I've got something off in my simple macro below.
[Code] .....
View 4 Replies
View Related
Jul 27, 2009
Column DColumn EColumn FTarget %DifferenceCash4%5.12%Large-Cap8.5%1.73%Commodities4%-1%
I need to color code Col F based on the following criteria:
If F2/E2>20%, then shade pink else no color. I only care about the absolute values. The 20% number can change depending on the category. So for commodities, the limitation is 30%. Like that I have a list of about 20 asset classes, 14 of which have the 20% constraint, rest are either 25% or 30%. I would like the cell color to be updated each time I import a file. Also I have other conditional formatting on Col F (like red color if negative number) so I don't know if another conditional format will work on the same cells?
View 9 Replies
View Related
Feb 26, 2014
I have multiple worksheets with different data which are as follows:
Sheet 1 - Pricing
Sheet 2 - Selections
Sheet 3 - Summaries (created later with a pivot table)
For the most part I have the sheet working as I want it to, the issue I have is on the selections sheet. The Selections sheet has data validation linked to the All Data sheet via a drop down list, what I would like it to be able to do is to display in the dropdown list 2 separate columns from the All Data list rather than just the primary list.
Allow me to elaborate:
Sheet 1:
Column A; Socket, Socket, Socket, Cable, Cable
Column B, Supplier A, Supplier B, Supplier C, Supplier A, Supplier B
So just to confirm I would like to be able to select the item from column A but it also display (while selecting), column B side by side so that a comparison can be made as part of the selection process.
View 5 Replies
View Related
Feb 15, 2010
I would like to be able to change the color of a cell in V4:AB31 and have the formula in AM10:AM13 automatically calculate the new result. As it is now the user has to press Ctrl ALT f9 for the formula to recalculate.
View 7 Replies
View Related
Oct 7, 2008
I have a workbook with a tab called "parts list" where there are thousands of parts with prices. I need to sum all of the parts based upon their location within the list. The list is divided with the following headers - Yellow (Assemblies), Gray (Sub-Assemblies) there can be many under a yellow header, and Green (Components) there can be many under a Gray header.
I have been summing the areas manually, and I am hoping for a more automated way of creating the totals by running a macro.
I need to sum (Column K) from Yellow+1 to Yellow-1 and deposit the sum in the starting Yellow row - Column L. This needs to be repeated for as many Yellow Headers as there are in the list. I then need to repeat the process for Gray Headers - Col K, Gray+1 to Gray-1, and put total in starting Gray header, Column L. Once again, there can be many Gray Headers under a Yellow header, and this needs t be repeated throughout the list. I then need to repeat this for the Green Headers - same as above Gray. I am hoping that this can be done by color, but if it's easier, I can sort the list by color and in column M, add an "A" to all Yellow Headers, "S" to all Gray Headers, "C" to all Green Headers, and "E" to represent the end of the component list -
View 10 Replies
View Related
Jun 10, 2009
There are 20 worksheets in my workbook. For the first ten, I would like to print out the only sheets in which cell E28 = 0. For the last ten, I would like to print out the sheets in which cell T1 = 0. Each sheet has a unique name.
View 9 Replies
View Related
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
May 24, 2012
I have the following data on a spreadsheet created by a formula:
A1: 1.1234
A2. 2.234
A3: 3.34
I have the data formatted to only have two decimal places so it looks like this:
A1: 1.12
A2: 2.23
A3: 3.34
I'm using the following formula in cell B1 to display the values of those cells in a single cell in order A1,A2,A3:
=A1 & "," & A2 & "," & A3
However when the values of A1, A2 and A3 appear in the in cell B1 they appear as: 1.1234,2.234,3.34
Is there a way I can edit the formula or format something so the values in B1 appear as with only two decimal places? i.e 1.12,2.23,3.34
View 3 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
Apr 14, 2014
I would like to count the number of orange cells, Green cells, Blue cells in the attached spreadsheet.
I have attached the sample file.. In my original file the cell colors will be a result of some conditional formatting.
Color Count test.xlsx‎
Test2.xlsx
View 3 Replies
View Related
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 24, 2007
I want to format a group of cells color based on the value of one referance cell. Example would be: If a1 is adn "X" the the results in cells b1 thru b10 would be green else they would be blue. Is there a way to do this using Excel 97?
View 3 Replies
View Related
Mar 28, 2012
The Excel file is in this link
[URL]
If you look at the chart on the left, it breaks down how this whole thing works. It takes the sum of the numbers that fall within each line. I wrote it out on column M so that it's more clear. In column D-L, I filled in random numbers ranging from 4000-700. The numbers in those gray boxes are ignored. This Macro should only apply to Column M and beyond. Is it possible to write a macro that will be able to do this?
View 1 Replies
View Related
Sep 3, 2013
I was handed a pdf of some leads by a co-worker. Finally, I got the data into excel but it's in a pretty useless format... 30,000 rows of contact info with no rhyme or reason. I want to upload this into our CRM, but I need to get all the different bits of info into separate columns. I'm no excel pro, but I managed to use an Excel plugin called ASAP tools to bring some order to this chaos... All names have a blue cell color, all titles have a green cell color, work phone is red, and so on.
Does anyone know a way that I can sort these into separate columns? I've trying playing with the Filter function, but it hides rows which makes the output useless for my purposes.
View 1 Replies
View Related
Dec 11, 2008
I'd also like to have it be able to change the 2 cells to the right, in columns (M&N) whenever the conditional value given below are true. I thought I had posted this message yesterday but never saw it show up as a thread.
Private Sub Worksheet_Change(ByVal Target As Range)
Set MyPlage = Range("L1:L800")
For Each Cell In MyPlage
If Cell.Value = "Hold" Then
Cell.Interior.ColorIndex = 3
End If
If Cell.Value = "Complete" Then
Cell.Interior.ColorIndex = 4
End If
If Cell.Value = "Issued" Then
Cell.Interior.ColorIndex = 43
End If
If Cell.Value = "Release" Then
Cell.Interior.ColorIndex = 36................
View 4 Replies
View Related
Nov 22, 2012
I want to highlight the cell of Column 'A' of excel only when it does not contain the text like "Verify", "Validate" or "Evaluate" in its content if, the value in corresponding cell of Column 'B' holds the value 'Y'. Secondly, the column 'A' can't contains the words like 'Verify', 'Validate' and 'Evaluate' if the corresponding cell in Column 'B' holds value 'N'. So just need to highlight those discrepancies if its there.
Column A
Column B
Expected Action
Press F3
N
Its Fine
Verify this..
Y
Its Fine
this....
Y
Need to Highlight Cell of Column A as Value in Column B is Y but the column A does not contain any value like "Verify", Validate" or "Evaluate"
Verify This.
N
Need to Highlight Cell of Column B as the Value of Column A contains words like "verify", "validate", "evaluate" but corresponding cell value in column B does not hold value "Y".
View 3 Replies
View Related
May 11, 2009
I want to change the fill color of all cells on my sheet, based on the value of one specific cell. In my sheet, I am using cell F1 as the trigger for the change. If the word Blue is in the cell, I want the background color of all cells to be Blue. Likewise for Red and Yellow as well. I don't believe conditional formatting can get this done, as all but the one cell (for this) will be empty.
View 2 Replies
View Related
Jul 1, 2008
I have been tasked with creating a Macro in order to help speed up a rather simple set of steps that are taken to format a spreadsheet each day. The last step, however, is not so simple. I would like to add a statement to the macro code that does that following:
Based on the value of a specific cell, color the cell and all corresponding cells within the same row (from colums A to AA) yellow. Essentially, cells in column D may have a the value "No Allocation", and if they do, I will need to format that cell yellow as well as all the cells within the same row for the colume range A to AA.
View 9 Replies
View Related
Jan 9, 2008
I know that I need to use Conditional Formatting, but I can't get the formula code correct.
This is what I need it to do:
If cell A2 is populated (not blank) AND cell B2 is blank, then cell B2 to be highlighted in bright red fill.
If cell A2 is populated (not blank) and cell B2 is also populated (not blank), then leave formatting as per normal
If cell A2 is blank then leave formatting as per normal
Basically, as soon as someone types anything into cell A2, cell B2 to turn red. Then once they have input something into cell B2, the red highlight can disappear. The inputs will be text, not numbers, so I can't use </>
I have tried varying circumstances of IFs, ANDs, NOTs, ISBLANKs etc and I can't crack
View 5 Replies
View Related
