Color Cells Within Matrix Based On Conditions Outside
Jun 5, 2008
In 'MAIN REPORT' there are thousands of columns of data, of which the important columns are 'Factory No.' and 'Row No' and 'Quantity'.
Each factory has its own worksheet as well.
In each of those factory sheets there is a 15x65 grid for each of the 65 rows that are mentioned in MAIN REPORT.
I have created and IF statement that checks if there is any data in 'Quantity row' and if there is it enters a "1" in the that cell of the virtual Factory grid.
This is the formula:
=IF('MAIN REPORT'!I2="","",1)
However, this simply checks that data while there is only 1 quantity for each row of each house.
Sometimes there will be different quantities (for diff. products), and I need a formula/macro that checks the line no. column and then,
If there are 5 items in row 7 for example then it will place five "1"'s in the first five cells of row seven in the corresponding factory sheet.
I was hoping there was an alternative to having to use so many named ranges for each sublist (there would many sublists in the actual workbook). Anyway, I'm a little uncertain about the code you posted - do the LIST and SUBLIST codes go in Source in Data Validation, or in Source in Dependent Validation Lists Based On Header Values? Can you give me a bit more instruction on this? Also, I'm trying to use the following code to color cells upon a change in value in V1 or V2, but can't get it to work. Can you kindly tell me what's wrong with it?
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> "V1" Or "V2" Then Exit Sub Select Case [V1].Value Case "A" Target.Interior.ColorIndex = 40 Case "B" Target.Interior.ColorIndex = 35 Case "C" Target.Interior.ColorIndex = 36 Case "D" Target.Interior.ColorIndex = 34 Case "E" Target.Interior.ColorIndex = 19 Case "F" Target.Interior.ColorIndex = 24 End Select End Sub
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".
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'm trying to get the font to turn red in column G if it is greater than or equal to column I, and get it to turn orange if it is between 80% of I and I. This is what I've got so far.
Sub Worksheet_SelectionChange(ByVal Target As Range) If Range("G" & Target.Row) >= Range("I" & Target.Row) Then Range("G" & Target.Row).Font.ColorIndex = 3 If Range("G" & Target.Row) >= Range(("I" & Target.Row) * 0.8) Then Range("G" & Target.Row).Font.Color = 45 End Sub
I am getting a "Run-time error:'13' Type Mismatch" when we try to paste a value (in this case "Y" or "N") in multiple cells at once. I did look up this error on the microsoft site and found this. http://support.microsoft.com/kb/821292. which didn't seem to be the exact issue I was having because I am not using a macro.
Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer Call ChangeRowColor If Not Intersect(Target, Range("$A:$E")) Is Nothing Then Select Case Target Case "y", "Y" icolor = 4 Case "n", "N" icolor = 3 Case "?" icolor = 6 Case "n/a", "N/A" icolor = 13................
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.
I am trying to add a third if statement to my existing code looking to see if a cell in a column is blank and the corresponding cell in another column contains a number that is greater than or equal to 1. I can't figure out how to write that part of my IF statement.
If Cells(r, 4) = " " And Cells(r, 3) >= 1 Then Cells(r, 1).Resize(1, 5).Interior.ColorIndex = 35 Set rng = Union(Cells(r, 1), Cells(r, 3), Cells(r, 4)) rng.Copy Worksheets("Sheet1").Range("G40:I40").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues) Application.CutCopyMode = False Cells(r, 5) = "NEW" End If Next r
I am trying to work with conditional formatting to turn a field a specific color based on two conditions, one of which is variable. The first condition is a list of ongoing events (which can change) and the second condition is a set of known milestones. I want to create a control (check boxes?) that will turn a given field (which has dates in it from another calculation) to either Red, Yellow, or Green.
I was thinking two Dropdown boxes - one for the events and one for the milestones. The user would choose an event, choose the milestone, then choose a checkbox or something to change the color of a field elsewhere in the spreadsheet. Right now, I'm doing this in a very clumsy way by having three checkboxes for each milestone for each event. There are five milestones per event (so 15 checkboxes per event on Sheet2). I will likely have 70+ events so I was hoping for a better way to do this. I've done this for the first three events and need a more efficient way of doing this.
See the attachment : Event Milestones based on Go Date Uploadable.xlsx‎
I am trying to auto-fill a matrix in sheet 1 with information from sheet 2. see the attached example. Only, in this case I put the values into sheet 1 manually. Is it possible to this without VBA? I am experimenting with an INDEX/MATCH combination, but thought I'd raise the question here in parallel.
wish to write an VB macro in Excel (2003) that populates a 6 row by 10 column matrix (for a 3 D graph) by inserting values from matrix row column headers in cells in another worksheet and using goal seek to derive a value which will be pasted in the respective cell in the 6X10 matrix
Specific example: Matrix (Row Headers A2 to A7, Column Headers B1 to K1, Cell values are in B2 to K7). Please note row and column headers are numerical values since this is for a 3D graph
Macro to be written
Pick a value (numerical) from row header A3 in Sheet A and paste it in cell B6 in another sheet (Sheet B). Pick anothet value (numerical) from column header D1 in Sheet A and paste it in cell B7 in Sheet B.
Run a goal seek scenario such that cell S10 in sheet B is equal to 50 by changing cell G8 in Sheet B
Copy the value chosen/optimised by Goal Seek from cell G8 in Sheet B and paste it in Cell D3 (which is the relevant cell for the values for intersection of headers A3 and D1) in Sheet A
Macro should populate all cells in the matrix in Sheet A (matrix cells to be populated are B2 to K7) in a similar fashion
The macro should be locked in to the cells which are either being copied from or pasted into so that it still references them even if new columns or rows are inserted.
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.
so let us consider that these are the prices for X company and i have another ten tables for other 10 companies, what i need to do is to create a unified table that includes all the companies prices for all the destination depending on the Country_Code as it could be considered as a primary Key and without any duplications and to include the all the data in all the tables as some destinations and prices are not available in all the tables, so the tables contains different data, the unified table should look something like this
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.
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.
I have attached a sample data sheet which i am working on . ITs a comparitive matrix trable with Input validation between 0&2 . I also want the table to be dynamic .If i want to increase or reduce no. of rows and clumns i should do it some how . More details are mentioned in the attached file .
I am writing a data validation macro which checks various conditions and highlights cells if they are incorrect.
i.e. If cell in column D = "specific text", then cell in column AH of the same row must = "specific number". If it is not that number, colour the cell red. If another cell in column D = "another specifci text", then cell in column AH of the same row must = "specific number". If it is not that number, colour the cell red.
What I've found is that I can get one condition of this nature to work, but if I run two or more in the one macro, that they supercede eachother (and thus the final one is the only one that works).
See below sample code.
Code: Dim val As Integer val = UsedRange.Rows.Count For i = 2 To val Step 1
I have an issue log containing (separate) columns with dates, numbers and text.
One column (A) has the status (open/in progress/pending/closed/re-opened) One column (B) has raise dates One column (C) has close dates One column (D) has the type of track (this is text, 3 types)
I want to count the amount of cells that have a close date before date x, given they are open (in Column A), and for a specific track (column D).
After creating a new sheet with a date range in one column encompassing all dates that occur in my column B & C, I tried to come up with a formula to count how many cells, and consequently how many issues, were open (or closed) on a particular date per track and as a whole.
So for instance; count cells in column C if Column C is before date 101112 and Column A is open and column D is "Build".
After trying COUNTIF and SUMPRODUCT the conclusion is I can't get it right.
I'm trying to write a formula where I can add the values found in non-sequential cells in a row based on if the value in the previous cell in the same row meets certain criteria.
I have a list of people to send drawings out to. I have already made the selections am just trying to work out how to simplify the output so as to use as a field in a data file to go onto a letter.
"| A01 | A02 | A03 | A04 | A05 | A06 | A07 |" are for Joe Bloggs ( | are cells)
In another cell, i would like to merge them together like so
"A01-A07" are for Joe Bloggs
And also as follows
"| A01 | | A03 | A04 | A05 | | A07 |" are for John Smith
would like this to be "A01, A03-A05,A07" are for John Smith.
So differing conditions depending on what happens in the range. I have had a couple of attempts at this using a VBA concatenate command, I can get them to display like "A01,A02,A03..." with a selected separator, but cannot get it how i want it.
I have 2 worksheets (X and Y) and am trying to write a macro/vba to copy certain cell values from a row if that row has a marked cell. For example, on sheet X, marked cell is column P and when P = "Y", then values from cells B, D, I, J, L need to be copied to sheet Y.
Sheet Y has multiple rows and the values from X need to be copied into appropriate rows. B from X goes into A from Y, D into B, I and J into C, etc.
Im not sure how to go about this other than have every cell copied and then filtering sheet Y. But Id like to have a macro only copy marked rows and appropriate cell values (some contain formulas) as the data is constantly changing and sheetY has to be printed up.
What formula do I use to populate certain cells (E5:E10 and J5:10) based on match with condition (E3) with cells from and in the ranges C2:C73 and D2:D73 without creating milelong IF formulas? I am almost at the goal... past 10pm here in Thailand and still at the office
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!
I am trying to count the number of unique entries in a sheet, that also satisfy 2 other conditions.
I've attached an example sheet : Example email report.xlsx
The result I need is: The number of unique values in column E, that also have NULL in column G and NULL in column I.
So, in the example, the result would be 7.
I know I can do this by conditional formatting and filtering, but would prefer a formula, and perhaps also a macro that I could apply as the number of rows in each sheet is up to 20000, and each month's sheet will be a different size.
I want the conditional format values to change Based on the value in Cell B2.
If the value in B2 is "old", then I want these conditional format conditions for A1:A6. Numbers between 1-2=green text, 3-4=orange text and 5-6=red text.
IF the value in B2 is "new" then I want the conditional format conditions in A1:A6 to change so 1=black text, 2-3=green text, 4-5=orange text and 6=red text.
I know how to do multiple conditional formats but i have no clue how to change multiple conditions based on another cells value.
I have a worksheet ("ALL JOBS") with huge row data and the row data will vary every day. My problem is there will be a lot of blank cells which I need to fill manually and its tacking lot of time of mine.
Code to fill these blank cells automatically with some specific texts based on the attached conditions.
I have attached the workbook for more details. FillCellsTest.xlsm‎
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.)
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.
