Mark Cells Where 2 Column Conditions Are Met
Sep 4, 2007
I have a macro that's supposed to update the value in column 1 when two conditions are met. Unfortunately it doesn't update the column at all:
Sheets("All_Records").Select
Dim item, rownum, maxrownum As Integer
Application. ScreenUpdating = False
maxrownum = Range("A2000").End(xlUp).Row
For rownum = 2 To maxrownum
If (Cells(rownum, 1).Value = "=") & (Cells(rownum, 29).Value = "OPEN") Then
With Cells(rownum, 1)
.Value = "APPROVED TRIAL"
End With
End If
Next rownum
End Sub
I took the code from another spreadsheet I have where it works fine. But there only one condition has to be met before the field is updated.
View 4 Replies
ADVERTISEMENT
Jan 23, 2009
I have a lot of data (regarding graffiti) in a table and all my formulas work well. But, because I sometimes make an error in two columns of data I sometimes get results that do not balance. I want to put in a conditional format (?Is there a better way?) that will highlight the error as I enter the data. Can you help please?
Using Excel 2003 (sp3) and no add ons. There are lots of identical rows, 5:400, making up a table. I will use row 10 as an example.
In column M there is a formula which may or may not give a visible result. (It's a measure of the number of days taken to clean the graffiti and will only hold a result if the job has been finished.) The result for "M10" will be a number between 0 and 5 90% of the time, but may creep up to 30. It may also leave the cell looking blank although the formula itself is still lurking out of sight.
A matching row of cells, O10:T10, have no formulas in them. I must manually enter a "w" or an "a" into ONE of these columns, depending on the graffiti location (Worthing or Adur) and property ownership (Columns for residential, commercial, council etc.)
So now the errors I need to flag up as I enter data are to ensure that M and O:T balance. There should always be a result in M if there is an entry somewhere in O:T and if there is an entry in M there must be an entry somewhere in O:T.
Sometimes I mistakenly put an entry in O:T even if there was no result in M. Sometimes there is a result in M and I fail to put an entry in O:T. I need to be alerted as this happens, if possible. It will save hours of trying to track down my data entry errors.
BUT, there is already one conditional format already in M. It highlights the cell if it goes over 5 days for cleaning. =ISNUMBER(M10)*(M10>5).
If you can help, thank you very much. I thank you, my wife thanks you for me not doing overtime trying to track down my errors, my boss's thank you for making me more efficient and other forum readers may thank you for helping them to improve their sheets.
View 7 Replies
View Related
Dec 6, 2013
I am trying to sum up cells from a column based on conditions in another column (see attached file). I know this could be done using a formula but for various reasons I want to use code. I am not sure if what I am doing is the right approach.
View 1 Replies
View Related
Feb 2, 2014
I have the following formula and need to incorporate that it is only a "B" if B20 also matches in named range "Breakdown_RSC"
Formula: ...........
I need the following section to take into account the data in B20 as in if it matches the data in the cell it should be a "B" in the cell.
View 2 Replies
View Related
Dec 18, 2008
i have a excel file i need to markup by a 10% value. It is the L column starting at row 2 till a varying number of rows. i need to mark up all of the rows in the spreadsheet. i do not know how to make it repeat.
This is the formula =L2*1.1(i think its the right one) but i do not understand how to make it repeat. any help would be greatly appreciated.
I need the value to be replaced with the marked up value.
View 8 Replies
View Related
Oct 17, 2007
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
View 2 Replies
View Related
Aug 14, 2009
I have a problem - I wanna to mark (to colour) just cells which I use in formula is there any option for thise
If I have some table and I have formula =A1+B5+C7 I wana to colour just mentioned cells
View 9 Replies
View Related
Dec 11, 2007
I have many wordlists, which have multiple entries in one column. I would like to mark up entires which appear more then once with numbers 1,2,3....The number should be put at the end of the word or ath the beginning.
Before Macro:
Column A
Love
Love
Long
Lonely
Lonely
Living
Living
After running the macro:
Column A
Love 1
Love 2
Long
Lonely 1
Lonely 2
Living 1
Living 2
View 10 Replies
View Related
May 7, 2007
I am looking for a way to ease a 24,000 line duplicate list into showing just the files from a specific folder/subfolder and its dupes. I have marked the files that reside in that folder by adding a column and entering a "1" in it. What I am looking for is a way to add that "1" to the others of that unique numerical ID duplicates, so I can sort and trim the list to just those files. End result is to show files in one folder and the locations of duplicates of those files elsewhere for eventual cleanup.
Very loosely, if C2=C1 and B1=1, then make B2=1 (and so on)
(A is blank and only there in case a formula needs input there)
A B C
110121
10121
118134
18134
18134
124232
24232
View 9 Replies
View Related
Jan 28, 2013
i have two entries in my excel sheet which i want to get noted in the other cell as follow
A B
1 ORDER NO. 480
2 GOODS 5
3 P.M 480X5
as my table have data in B1 AND B2 so i want B3 automatically becomes 480X5 so how can i do it
View 2 Replies
View Related
Oct 4, 2007
Can I add something to a cell and have it so it does not print?
I need to mark certian cells to have data filled in, in those cells. However if data is not put in those cells I need them to be blank when printed.
Example:
I can put text in B7:
Enter Name Here
Then the user knows to put the name in that spot. If there is no name to be entered then I want to be sure that is not printed when I print the list.
Is any of this a possibility without putting 33 different labels on the page, which is one way. Or 33 different comments on the page, another way.
View 9 Replies
View Related
Dec 16, 2007
I have 2 lists in column A and column B. For each cell in column B, I want to put a "YES" in the corresponding cell in column C if the contents (of the cell in column B) are somewhere in the entire list under column A (I think the list in both columns is about 5000+).
I have logged in after quite a gap and found that all my subscribed threads (gathered over a year) have vanished
View 3 Replies
View Related
Feb 22, 2008
Column-A starts out empty.A user changes cell B5 and navigates to another cell (whether by clicking, tabbing, or enter-key, it doesn't matter).An "X" is placed in Column-A for that row.Preferably the user would then be taken to the cell they were navigating to (whether by mouse-click, tabbing, or the enter-key).This happens every time a change is made to a cell.I have no idea how to code for this.
I'm not sure what constraints I might have for exceptions. For instance, can it handle multiple row being pasted into or cleared at once, etc. ( Deleting columns will not be allowed in this instance.)
------------------------------------
ADDENDUM:
I continued my search and found the instructions at [url]
My ultimate goal in putting an "X" in Column-A is to identify rows with changes, so they can then be copied into an "archive" worksheet. I'm thinking this VBA version of track changes would be much better than what I was earlier thinking.
I have a workbook with two worksheets, Data and Archives. I want to let a user make changes to data in the "Data" worksheet. Then when they save or press a button all rows with edits will be copied into the "Archives" worksheet.
Appending date/time and username stamps in the trailing columns would be very handy too.
View 9 Replies
View Related
Sep 5, 2013
I have a goal set for the year. With conditional formatting, each month will be set to either red or yellow based on where it stands in relation to the goal. I would also like to show an increase or decrease in productivity for each month. I tried to use conditional formatting with the formula:
=IF(C2=D2, "", IF(C2<D2,"↗","↘")).
The formula works on its own without the conditional formatting so I am not sure what I am doing wrong. I've attached a sample with all the conditional formatting and formulas in place.
View 5 Replies
View Related
May 6, 2009
I am wanting to count the number of records (excluding cells with no value) based on criteria in a corrosponding column. In column "AS" I have a number of records that are not sorted showing values "7", "13" and "2".
In column "AL" there are values attached to some of these records based on certain IF statement conditions. I am wanting to count and sum the number of records in column "AL" that meet the conditions of "7" in column "AS" and so forth.
View 3 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
Feb 15, 2014
I have a table(see attached). I want to sum the total hours worked in Col C. Col B has the number of people on the job (either 1 or 2). I need a condition, I guess , so that if Col b has a number 2 in it, then the adjacent cell needs to be multiplied by 2, then summed into the total hours worked.
I am not sure on how i would write a formula for this. Looking at the sample if I add the total hours from col C at the moment I get a total of 18 Hours but as cell C3 & C5 have a "2" in their adjacent column, the total hours answer should be 27 hours.
View 3 Replies
View Related
Jan 16, 2009
I am trying to get sumproduct to work on a table where two conditions in the same column must be true (ie two employees must both have worked on the same project) in order to calculate a result. Trouble is my formula doesn't produce anything but a big fat zero.
Column A contains the list of projects. Column B has the list of employees. And column C has each employee's cost. So:
=sumproduct((column A = project1) * (column B = Joe) * (column B = Mark) * (column C))
=total cost of Project1 when both Joe AND Mark work on it.
Unfortunately, when I structure sumproduct this way, it returns zero.
View 9 Replies
View Related
Feb 18, 2014
I want to display the cell content after it has verified two things: 1. It pertains to the running month 2. It has maximum serial number The file enclosed is self explanatory. The formula would be dragged as every day data is likely to be added. I have written a formula, but I get error.
View 4 Replies
View Related
Jan 11, 2010
I would like to use a SUMIFS statement to sum up a column of numbers based on two conditions. The first condition is a word. I have this done. The second condition is going to be a two week date range, which I am having problems solving.
Here is my formula that works so far.
=SUMIF(Data!C:C,"Office",Data!D:D)
Now I would like to SUMIF a second condition is met. The second condition is a date and time in this format.
1/9/2010 9:21:49 AM
I am only concerned about the date. Time does not matter. I cannot figure out how to specify a date range for the second condition. I am trying to specify between 1/1/2010 and 1/14/2010 as a test. So here is what I have so far with a SUMIFS.
=SUMIFS(Data!D:D,Data!C:C,"Office",Data!A:A,"Cannot figure out how to specify dates")
Maybe my logic is wrong...maybe I need to do three conditions? Where column A has a date < 1/14/2010 and a condition that specifies a date > 1/1/2010.
View 14 Replies
View Related
Mar 2, 2006
I have a report in which I need to delete the entire row for each cell in Column A that has the name "Defacto" in a certain location in that cell. I am trying to use VBA code in conjuction with the "MID" function [i.e., Mid(Cell.Value, 8, 7) = "Defacto"]. This is the code I came up with (but, obviously, it doesn't work):
Sub DeleteRowOnCondition()
Range("A2").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
For Each Cell In Selection
If Mid(Cell.Value, 8 , 7) = "Defacto" Then Rows.Delete
Next Cell
End Sub
As well, there is another worksheet in the same report in which I need to delete all the rows that do NOT meet that condition (while retaining the ones that do).
View 9 Replies
View Related
Jul 25, 2007
I have a lot of data broken up into numeric decimal form in one column. I need all this data to be transferred to different worksheets based on the amount of decimals. Attached is some of the data I need sorted. The first sheet is my main sheet, then the next sheets need to be ascending in numeric order by sorting the data with two decimals under the column Tear number system.
For example I need the next tab to be the data with 0.21 with 2 decimal places, then the next tab would be the data under 0.28 with 2 decimal places, then the data under 0.29 with 2 decimal places. So the 0.21 worksheet would be 0.21 and have all the data for 0.21.1, 0.21.2 all the way through 0.21.6.
I also need this to be compatible with new inserts to the main table. Meaning if a new number is entered in, the number and data would automatically be entered and sorted to the tab that the number pertains to. I have tried a vlookup function but have not had much success. I have also tried recording a macro but am having problems with that as well.
View 9 Replies
View Related
Nov 13, 2011
With a range of cells (a1:ao41) I want to color several cells depending on the value of that particular cell and another color when that particular and a corresponding cell have both the same value. It's some sort of sociogram The first row and column both contains names of students. Let say the students have to choose the three most populair ones to work with and the three most unpopulair ones. Positive ones get the color blue, negative ones get the color yellow. That's easy to do with conditional formatting. But now comes the tricky part. If for instance Student 3 chooses Student 7 and Student 7 chooses Student 3 the color of both cells have to be green if both positive and red if both negative. That's also do-able with conditional formatting, but it most be much easier to do with VBA. I just don't know how .
View 5 Replies
View Related
Mar 14, 2012
How do you highlight a row when conditions in two cells of that row are met? For example, I have a spreadsheet with a STATUS column ($A) and a TYPE column ($E). When the status is "open" and the type is "1", I want the row to be green. But when the status changes to "closed" I want the row to be a different color or no color at all. How do I do this?
View 3 Replies
View Related
Apr 16, 2008
I have found several forums with this question but none of them seem work/fit my needs
What I need to do is look in Sheet1 at column 'B' begining at row '15' (row 14 is table header) and if the cell contains "Yes" then copy 3 cell directly to the right (C15:E15 in the case of the first row) into Sheet2 into cells B15:D15 then do the same for row 16 and so on up to row 600
Creating a cut down copy of the Sheet1 which does not contain the row which determines if it should be copied.
View 9 Replies
View Related
Aug 13, 2006
I have a spreadsheet which I need more than 3 conditions to the formatting - is there anyway of doing this in VBA!!! Its for a holiday chart that i'm doing - some of the items they can enter into one cell is: B (Bank Holiday) / 1 (1 days holiday) / TR ( Training day) / SL (Study Leave) / 0.5 (half day hols) and more.
View 2 Replies
View Related
Jul 12, 2007
I have a Macro that when run checks for the lowest value in a line & highlights that line. It works just as it should, infact it works better than I had planded. When I update a cell value and move to the next cell it changes the highlight.
Option Explicit
Sub SELECT_LOW_PRICE()
Range("a4:i126").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=and(a4=min($a4:$i4),not(isblank(a4)))"
Selection.FormatConditions(1).Interior.ColorIndex = 6
Cells(1, 1) = "Updated " & Now
Cells(1, 1).Select
End Sub
View 6 Replies
View Related
Sep 13, 2007
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................
View 2 Replies
View Related
Jan 17, 2010
I tried to ask this question yesterday -- but it was a follow-up question stuck at the bottom of a thread. So, with your indulgence, here is a simpler version of the question, complete with an attached spreadsheet, if you wish to use it. I also closed the other thread by marking it "Solved", since it answered my initial question.]
The situation:
I have two columns of data. The data is not in alphabetical order, and every column includes duplicate values.
namegender
jones m
martinf
smithf
collinsf
wilsonm
jones m
martinf
hughesm
wilsonm
martinm
smithf
west f
jones m
west f
martinm
The challenge:
In one cell, count the number of unique names that appear in the name column 3 or more times... with the additional condition that each unique name (which appears at least 3 times) must include at least one one woman!
The correct result: ...
View 6 Replies
View Related
Feb 21, 2008
How do I calculate the average based on multiple conditions? Is my syntax incorrect?
Here's what i'm looking for:
=average((IF(X1:X1000=A1)*(Y1:Y1000=B1),AA1:AA1000))
View 9 Replies
View Related