Compare Cells For Conditions & Color Corresponding Cell
Sep 26, 2007
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 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 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................
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 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
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.
The code below turns numbers and dates on my worksheets(1) to text priorities on my worksheets(3), I now need to color the cell backgrounds of the results on worksheets(3). With formulas I know I can conditional format up to 3 colors (or 4 utilising the background) but I am after 6. I also know that you will look at my code and laugh as I should have done the color coding with the initial commands, but I struggled with it for 2 days and simply couldn't get it to work.
In the VB below it all works off a command button and up to the third color it works however it then gets stuck and throws up a runtime error '1004' Application defined or object defined error. I did get around this before by recording macros and effectively sticking them together but it hasn't worked on this occasion...... any ideas??? (I hope I'm not breaking any rules by pasting the code directly in?)
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 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 two different tables, 1 table gives the conditions you need for specific stations. Stations is shown vertically (10 to 150), and conditions people must have are shown in the top row. (Alphabet). This data is given and can change alot. If I compare this data, a new table will be created that compares the conditions per station with the people if they have it too. Then it will need to give an answer, 'Yes or 'No''. Yes means he has met all the conditions needed, so has all the competences needed for the station.
I tried to do this with normal formulas, but no success. I can do it by doing like 100 IF's in 1 formula, but that isn't efficient.
But that only compares if the person has exact the same competences, if he has more then the needed on 1 location, a NO will be returned. Same with count, that doesn't work either, if he has the same amount of competences needed for example station 10, he is allowed to, but he doesnt have the competences that are needed for that station..
I have two excel files and i would like to compare/merge with conditions. I have 9000 different items and these items i am getting from my distributor in an excel worksheet format. This pricing will then be imported into a point of sale system, but before i import it into the point of sale i need to merge the old pricing from the point of sale to the new distributor pricing.
The problem i have is not everything is in stock and they use the word "CALL" for non stock items. What i am trying to do is have a macro or a program to compare both spreadsheets by searching all item skew rows with call on the pricing column on the same row as the item skew and then not to merge with the old pricing point of sale unless the point of sale doesn't have this new skew. After this has completed then merge the new pricing to the old pricing together. The new pricing item numbers are on column A
B = Description C = Cost D = Item Name or (Description 2) E = Margin F = retail price
Old Pricing on worksheet Inventory A11 = Description B11 = Item Name or (Description 2) C11 = Item Numbers F11 = Cost G11 - retail price
The main reason why i need to do this is because when a customer would like pricing from our point of sale system, I wouldn't like to tell them that i have to call my distributor each time. I would rather have a little older pricing than no pricing at all.
I would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND($J2=0,$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard
I am trying to compare two types of conditions, one that has 3 variables and the other that has 8 variables (each variable has a numeric range), which places the correct result in F6 and F7 of the atatched spreadsheet.
I have a sheet of data (Sheet1 below) running into almost 1000 rows. Each month, I get an updated sheet (Sheet2) which has overlapping data with sheet1 (rows starting with N, O, P, Q), new data (rows starting with R, S) and data present in Sheet1 but not in Sheet2 (row starting with M in Sheet1). The columns "Item" and "Quantity" in sheet1 are populated by me and are blank in sheet2. The table below explains the structure.
Sheet1 Name ID Date Item Quantity
M 231 14/03/2001 egg 5
[Code] .......
It gets tedious to update Sheet2 every month. Is it possible to merge the 2 sheets into a Sheet3 (like below) if Name, ID, Date in each row is an exact match?
Sheet3 Name ID Date Item Quantity
M 231 14/03/2001 egg 5
[Code] ........
Further, it will make my job much easier, if it was possible to identify rows only present in Sheet1 and add a tag "absent in new" in a new columnidentify rows only present in Sheet2 and add a tag "new" in the new columnidentify rows with matching data in Name, ID, Date in Sheet1 and Sheet2 and add a tag "current" in the new column.
Something like the table below:
Sheet3 (Desirable) Name ID Date Item Quantity Comments
Is there a way to make a cell populate certain text based on conditions of other cells without putting the formula in the cell you want to populate. So that someone could type other text into the cell if the conditions were not met?
In the xls for each step I have 2 raws-planned and actual. Step planned duration is populated manually over the weeks.Before that row we have another reflecting the actual step status per week
I would like to find a way how cell reflecting the actual status of a step can be automatically populated (coulored) based on the colour/value of activities that are planned for that week and for that step.Activities are listed below the step and again have planned and actual row.
The rule should be : if for a week we have several activities all of them should be finished in order to have step stataus auto populated as green. If a single activity planned for that week is not done-then weekly step status should be red.
The activities for each step are grouped below the step. It seems that one of the difficult part in that request would be how formula will understand where starts and finishes the activities that belong to one step. To get that happen I placed a column showing step and another column where we activity.
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.
=AND($AE$2:$AE$10<=$F$2:$F$2000;$AE$2:$AE$10>=$E$2:$E$2000) With this I am checking for conditions to be true or false
Lets say I am checking AND(AE2 <= F2 ; AE2>=E2) this tells me if date in cell AE2 is smaller or equal to Cell F2, but how can I set this fromula to check if all the cells from F2 to F1000 are equal to AE2 so If I write date into Cell AE2 it checks all dates from F2 to F1000 and check if condition is true or false (and if I write date in cell AE3 it should check all the dates from F2:F1000, if date in AE3 is in this range)
I'd like to compare two cells, and if the first cell is either >150, <161, OR =165 AND the second cell is ="E" to output a 1, and if false, output a zero. I have =IF(AND(AND(Sheet1!B3>150, Sheet1!B3<161, Sheet1!B3=165 ),Sheet1!E2="P"),"1","0")
But i know that is incorrect because Sheet1!B3 can not be greater than 150 AND less than 161 AND = 165. I can't figure out how to make it >150 AND < 161 OR = 161.
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've a spreadsheet and I need to find cells in Column A that contains certain text and subsequently being able to compare the data in 2 other cells (column F and Column J) and post the results of comparison in Column L. Please see the tables below:
In this table, if Range("A1") contains abc, then data in Range("f1") would be compared against Range("J1"). If F1 = J1, then Range("L1") would say "ok" otherwise "needs to check" as in Rows 2.
I have a if function that i am creating in excel 2000, and it has 8 variables. I am trying to put it in a macro as it is not working as a nested if function. Here is the current if function...
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‎
