I need a formula that will highlight the entire Row when the criteria is met.
If Cell In column A = "Management" Then
Rows().Select
Row.Font.Bold = True
End If
If Cell In column A = "Scotia" Then
Rows().Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If
So I played around with the macro i posted on the previous thread, two allow me to compare two columns of data to see if they contain the same information, and have come up with the below, which works almost flawlessly:
[Code]....
The idea being that it compares the two rows of data, then in the colmun to the right puts a y or n if the data is contained in both cells. I have added in some code that related to highlighting those cells with 'n' in the allow me to see it much easier. The macro works fine, however it seems that when it runs, it will sometimes highlight random cells not in column d, for example colum g etc.
I have been trying to find a way to highlight columns that are filtered in Excel. On a laptop it is not always that easy to tell which columns are filtered. I have found a piece of VBA that can do this (see http://www.vbaexpress.com/kb/getarticle.php?kb_id=365).
I would like to do two things:
1. Have this run whenever an autofilter is changed (a new filter chosen or a current filter changed).
2. Have this effective for every workbook that I open.
I'm hoping that someone can help a VBA newbie sort this out.
I am trying to write a macro in VBA excel 2010 that compares 2 sheets.
The macro should be something along the lines of if column 7 on sheet 1 = column 1 on sheet 2
AND
on that same row if column 6 on sheet 1 = column 3 on sheet 2
highlight green
** also on sheet1 there can be the same batch ID so if it is the same batch ID it needs to calculate the sum and look at that amount...
Sheet1:
settleid
min Tran Date
Payment Vehicle
total Deposit
total CF
total MRI
RMBATCHID
475-T
03/03/2014
Connect
$562.95
$19.95
$543.00
6G000001450835
[Code] .......
Results >
Sheet1:
settleid
min Tran Date
Payment Vehicle
total Deposit
total CF
total MRI
RMBATCHID
475-T
03/03/2014
Connect
$562.95
$19.95
$543.00
6G000001450835
[Code] ..........
how to do the formula for highlighting whole row after entering a date in one of the columns.this spread sheet had over 300 rows so I can't do the conditional formatting, I need a formula for whole spreadsheet.
View 11 Replies View RelatedI am trying to do is I have column C and column G. For each row, I want to highlight or change the font color of the greater number. I can do it with conditional formatting, but I am only able to do it for 1 row at a time and it will take forever considering there are over 800 rows.
Is there an easy way to compare the two columns and highlight or change the font color of the higher number in that row?
For example:
C G
4 5
8 1
6 2
9 11
So it would highlight the 5 in the first row, the 8 in the second row, the 6 in the third row, and 11 in the fourth row.
Well I spent some time yesterday trying to figure this one out. It was only last week that I have taught myself how to use VBA in excel. However this looks like a though one.
Simply what i would like to do is highlight a column of a certain date (till a certain row number) based on a list of dates.
eg. Across the top I have a set of dates and on another sheet i have a list of dates.
I understand that there is a VBA code for such conditional formatting but i can't get my head around it.
I found this spreadsheet on here and I have been trying to customize it to what I need. I am trying to have scores from skins match highlighted. I want only the minimum score to be highlighted but if there is another duplicate minimum score I don't want it to highlight anything. I also need to find a way to count the skins won by each player and have it off to the side.
For those not familiar with golf a Skin is a game where you try to get the lowest amount of strokes on a specific hole. Ex- 4 people play the hole one. P1 scores 4, P2 gets a 3, P3 and P4 get 6. The skin would go to P2 who has the lowest score on that hole.
Highlight lowest number in each column not highlighting if there are duplicates starting at L6 down to L11 and for each column till AC. And the same for the group just to the right on attached file.
On row 13 and 14 it tells me who won a skin. I want to tally up the total skins won by each player. so if Joe's names shows up twice on R14 I want it to tell me somewhere in the sheet Joe = 2
I've got a sheet where I've got products in one column.
The second criteria is "week #" for which the row remains consistent but the column varies e.g. "week 8" could be in cell L12 today and in L18 tomorrow.
I would like to return the value of the cell at which a particular product and week intersect. e.g. if "product a" is in B20 and "week 8" is in L18, I want the value of cell "L20" returned.
Product to determine Row #, Week to determine Column # for returning value.
If they interest multiple times, I would like to sum up the values intersecting.
Neither product nor week # is unique in the sheet.
1) If B1:2008 is "EA" (and so on for about 7 other classes) then count how many days are entered in G8:2008 so a cell on a seperate distribution sheet tells me how many sickness days in total have been taken by EA's (and then the same for the other classes).
View 7 Replies View RelatedI am searching for a formula that will evaluate a criteria that would be entered into a cell. I then need the total from the columns up to the point the criteria is met.
A simple example below. The criteria is in (what would be B1). Since that criteria was entered then the sum of the values in columns B, C, & D would be totaled. Excluding column E, or 516. If the value was changed to July-14, then 516 would be included in the sum.
PeriodJun-14
Apr-14May-14Jun-14Jul-14Total
XYZ 506 509 512 516 1,527
I am trying to create a formula that will sum up a value in one column if certain critieria residing in other columns are met. I have attached a sample and the criteria.
View 4 Replies View RelatedI need to compare data across two columns: the date a claim was filed (column T), and the date it was resolved (column AH).
On my summary worksheet, I'm displaying the claim details for each month. I need to show how many of the claims filed (T) in each month are resolved (AH) - and for this output I'm not concerned about when they were resolved. This lets us track if there's still money due to flow out of the coffers for old claims. For example, there were 5 claims filed in July, and 4 of them are resolved. I want my summary tab to return a value of "Completed" beside July when I enter the resolution date for that last one, and "Pending" until then.
All the fields I'm describing are formatted as dates - including the name of the month (eg, "July" is displayed because I've put a custom format of "MMMM" on 7/1/08).
I've been playing around with countif and sumproduct but can't quite figure this one out.
Countif must meet criteria in 2 different columns. I can get one criteria in, but not both.
View 3 Replies View RelatedSo I have 3 columns in this example. I want to average column K if certain criteria are met in Columns G & I.
Columns G & I have text values and Column K is a number value.
Does that make sense?
If I'm simply counting how many meet both criteria, I have this: =COUNTIFS(Data!I2:I282,"Waiting",Data!G2:G282,"Sev1")
but I want to now take those and average the values for Column K.
What I'm trying to do count the TRUE values in multiple columns, if the criteria is correct in another column.
I've tried countifs but end up having the company included into the count, or only count the row that matches all the criteria.
If I do =COUNTIFS(A2:A7,"A",B2:B7,"TRUE")+COUNTIF(C2:C7,"TRUE") then I get 5.
When I change it to +COUNTIFS(A2:A7,"A",C2:C7,"TRUE") it works but there's a time where I need to check up to 8 Options.
Company
Option 1
Option 2
a
True
True
b
True
False
c
True
False
a
True
False
a
True
False
b
True
True
On the attached example i would like a formula in F2 which sums all the values where column equals "Smith" and column B equals "Golf European". I'm assuming it would involve the SumIf formula but i'm not sure if this is the best solution when the criteria is in two columns?
View 3 Replies View RelatedI would like to return the value in column D (Store Name) that corresponds to the Max value in column N (Units Still Required). However, this Max value must meet certain criteria. That is, the State (column J) and Style Code (column Q) must be the same as that of the row being considered.
I have tried the below formula, and it appears to work the majority of the time, however, occassionally it does not adhere to the criteria (i.e. same State and Style Code).
For example in cell M7:
=IF(L7=0," ",INDEX(D$7:D$999,MATCH(MAX((IF((J:J=J7)*(Q:Q=Q7),N:N))),N$7:N$999,0)))
CTRL + SHIFT + ENTER
I'm trying to create a short script which performs the following:
Searches for a specific name in column C on my "Resource List" sheet, then When this is found, check column H to see if this is populated,Then when both criteria are met delete the entire row.
Using a link I found here [URL], I've put together the code below:
[Code] ....
The problem I have is that although the criteria are being met the row is not being deleted.
Macro below would be to only go down to the cell = to the bottom-most cell with data in column A.
These columns below are all in row A (the title row)
If column named "paper" says "white" OR "grey" AND
column named "pizza" says "mushroom" AND
column named "size" says "large" AND
column named "flavor" says "butter pecan"..
(again, only going down to the same matching cell with any data in column a)
Then delete these columns.
I'm making a spreadsheet that tracks instances when something goes above or below a threshold. Anytime it goes below, a column populates with the number 1, and if it happens to go below a threshold on a Friday, a separate column populates with the number 2. If its doesnt go below, the column with 1 does not populate, however, every Friday populates with a two.
I need a column that adds the 1 and the 2 when the threshold is broken on a Friday, but only shows the 1 when the threshold is broken on a non friday and shows 0 when the threshold isn't broken, but happens to be a Friday.
I'm not sure if this is the correct way to display my sample data, but here it is (how to change it. I looked in the guidelines, but I can't download anything onto this computer, so I can't get the HTML maker)
A
B
C
D
E
F
G
H
I
1
Descriptor
Group
Identifier
Inventory
Total Vaue
Invetory Type
Group
Total Incomplete Inventory Value
[Code] .......
Here is what I would like in:
The sum of the total value of half finished and unfinished inventory for each group.
So, for group one, it would be 145. For group two if would be 38, and group three would be 316. (displayed in column I)
I think an array formula would do this, but I'm not really sure how this would work.
I have two columns with numbers in ranging as follows:
2
2.5
3
3.5
4
4.5
5
On the spreadsheet it looks like this:
4.5
5
4
4.5
3.5
3.5
3.5
4
3.5
3.5
3
3
3.5
3.5
What I need to show in the next column is a True where these two figures add up to 8 or more. However, the minimum in each column needs to be 4 and 4 i.e. if one column has 3.5 and the next 5 then return a value of False.
I have a data ranging from J:EW with a heading in row 2 e.g. J2 the heading is "OT1 Hrs"; K2 the heading is "OT1Amount"; L2 the heading is "OT2 Hrs" and so forth. My difficulty is, it is too much multiple columns from J to EW, how can I draft the vba to hide a column that has "Hrs" in row 2 heading instead of doing it one by one.
View 3 Replies View RelatedTrying to create a dynamic vba code that searches the second last active column for a particular criteria. In this case it is the value "901".
If the criteria is met it will search the row to see if value is >=0 then change to zero where applicable.
In the example, I have a loop which searches Column 6 for the matching criteria. I want to change from Column 6 to "second last column" which is Dynamic to allow for more column to search through.
The two codes below search through column "A" and Column "B". Is there anyway to make this Dynamic also so it searches from Column "A" to the 4th Last Active Column.
So the Column to check for Matching Criteria is "Second Last Column" The Data to Loop through will be from Column A to the "Fourth Last Active Column".
Public Sub ChangeCellValue_TwoConditions_ColA()
Dim Last_Row As Long
Dim iLoop As Long
Last_Row = ActiveSheet.Range("A65536").End(xlUp).Row + 1
For iLoop = Last_Row To 1 Step -1
If (trim(ActiveSheet.Cells(iLoop, 1).Value) >= 0) And (trim(ActiveSheet.Cells(iLoop, 6).Value) = "901") Then
ActiveSheet.Cells(iLoop, 1).Value = "0"
[code].....
Basically, need to count rows that have 5 columns of data in it. If all 5 columns within a row have something in them greater than one character, that row needs to be counted.
View 1 Replies View RelatedI am having trouble coming up with an algorithm for deleting columns, based on a certain set of criteria. Heres the issue, all the columns have a "1" the top of column . If that column has a cell number that's greater than or equal to 0.90 or less than or equal to -0.90 then that column gets deleted, if it just has a "1" then the row doesn't get deleted. Very complicated set of criteria.
View 6 Replies View RelatedI am trying to figure out how to create a formula using multiple criteria in different columns. Ideally, I need to use the whole column (i.e. E:E rather than E2:E400) because I don't want to have to update the formula every time I input data.
I will simplify my spreadsheet for example purpose. Basically, column A has a unique identifier that either begins with an "M" or an "R." Column B either contains a person's name or a "-". Column C contains a dollar amount.
1. I need to be able to count all the cells in Column A that begin with an "M" AND have a "-" in Column B.
2. I need to be able to SUM the $ amounts in Column C ONLY for the items that begin with an "M" in Column A and have a "-" in Column B.
Is there any sort of formula that might do this? I have tried SUM arrays but as I said before, I would rather be able to use the whole column.
I am trying to run a lookup on a rather large table.
Column A Column B Column C Column D Column E Column F
Postal code City Province Postal code City Province
I am trying to look up the City and Province based on the Postal code and can't figure out how to do this.
There are too many Postal codes to fit them all in Column A, I have tried V Lookup, Index Match and can't get it to work.
Sumproduct help with criteria in Columns and Rows
View 9 Replies View Related