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

How Can I Use Conditional Formatting To Mark Up This Set Of Error Conditions?

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

Sum Cells In Column Based On Conditions In Other Column

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

Mark Cell By Column And Row Value

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

Macro To Mark Up A Complete Column By 10%

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

Test X Conditions In Conditional Formatting: Highlight Cells Is Two Conditions Are Met

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

Mark Formula Cells

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

Mark Multiple Entries In The Same Column With Numbers

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

Mark Duplicates That Are Identified In A Seperate Column

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

Joining Two Cells Data With A X Mark In Between Them

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

Mark Cells For Entering Data

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

Mark Common Cells Between 2 Lists

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

Mark Changed Cells For Archiving

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

Conditional Format To Mark Increase From Previous Column

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

Count Data In Column X Based On Conditions In Column Y

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

Color Cells Based On Multiple Conditions Of Different Cells

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

Sum Column With Conditions

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

Sumproduct Where Two Conditions In The Same Column

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

Search Column With Multiple Conditions?

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

Trying To Use SUMIFS To Sum A Column Based On Two Conditions

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

Vba To Delete Rows Based On Certain Conditions In Column A

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

Transfer & Update Data From Column With Conditions

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

Coloring Cells By Conditions?

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

Highlight Row When Conditions In Two Cells Are Met?

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

Copying Cells Under Certain Conditions Using VBA

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

VBA Formatting Cells With More Than 3 Conditions

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

Format Cells On Conditions

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

Color Cells By More Than 3 Conditions

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

Count Unique Entries In One Column That Meet Conditions

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

Calculating The Average Of A Column Based On Multiple Conditions

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







Copyrights 2005-15 www.BigResource.com, All rights reserved