Highlight & Move Cells Based On Criteria

Sep 7, 2009

I have 3 data series, which are each futures contracts. Each of these futures contracts (the one on the left expires soonest) has an expiration date and as that date approaches, one will need to replace it with a new futures contract (the data series immediately to its right) having a later expiration date. This process is called a ‘roll’. I am seeking to create a continuous data series, which will incorporate the appropriate ‘roll’ based on two rules.
The rules are as follows:

1) Where the volume of the 2nd contract exceeds the volume of the 1st contract for 10 consecutive intervals, roll on that date.
2) Once the roll date has been identified, roll at the specific time interval on that date that has the highest aggregate volume (between the 1st and 2nd contract).
I have laid out the original data in a tab entitled ‘original data stream’. This is how the data will appear at first. In the tab entitled ‘Roll Example’, I have shown what I would like to happen to the original data streams. You will notice that in this tab, the data series are the same as the previous tab, except that they are not aligned as before. The data series to the left ( cells B4: D808) expires ..................................

ADVERTISEMENT

Move Range Of Cells Based On Cell Criteria

Nov 16, 2007

I'm trying to do is loop through a range of cells, and if the cell meets a condition I want to move it and 2 cells to the right of it left by one cell.

Sub MoveRangeOfCellsBasedOnCellCriteria()
Dim myrange, cell As Range
Set myrange = ActiveSheet.Range("H2", Range("H65536").End(xlUp))
For Each cell In myrange
If IsNumeric(Left(cell.Value, 1)) _
Or Left(cell.Value, 5) = "UNIT " _
Or Left(cell.Value, 4) = "THE " _
Or Left(cell.Value, 5) = "FLAT " Then
Else
cell.Range("A1:C1").Select
Selection.Cut
cell.Offset(0, -1).Range("A1").Select
ActiveSheet.Paste
cell.Offset(1, 1).Range("A1").Select
End If
Next cell
End Sub

View 7 Replies View Related

Move Row Based Off Criteria

Nov 8, 2007

What I am trying to do is if on the (Open Project Issues) tab column F says "Closed" it will move that row to a tab called (Closed Project Issues) tab. I was trying something like this:

Sub testmove()
Dim c As Long
For c = ActiveSheet.UsedRange.Rows.Count To 2 Step -5
If Cells(c, 6) = "Closed"
Rows(c).Cut
End If
Next c.......

View 3 Replies View Related

Move Rows Based On A Criteria

Jul 21, 2007

Cell a1:a3000 contains a lot of trackingnumbers. Cell b1:b3000 I have a "IF" formulas in place to write "ok" if part of the data in "column A" matches my criteria. What i need is a macro that will cut all the rows showing "OK" in column B from "sheet1" to the next availible row in "sheet2"

I have tried converting copy macros to Cut macros, but thye have not worked. I am rather hopeless for the coding part and mostly make macros based on the record feature. Also looked at the ones that have been showed here, but my skills do not make me able to change them to fit my excel sheet. About 8000 rows with data is added into this sheet everyday and the criteria is not always present. That means on some days there will be no "OK" showing in column B

View 3 Replies View Related

Highlight The Cells When Criteria Met

Jul 16, 2007

=IF(AND($P2="Yes",COUNTIF($L:$L,L2)>1,$Y2="Market Specific"),TRUE,FALSE)
But it is not doing exactly what I want it to, and I am struggling to work out what I need to change to get it to do so.

I want the formula to do is look at a three Columns of data, and to highlight Cells in Column Y when the following criteria are met:

The Row is active (determined by the "Yes" part), that the Row is "Market Specific" and that there is more than one Row where these Criteria are met (this is where I thought COUNTIF would work, but it's not).

View 9 Replies View Related

Identify/highlight Maximum Value Based On Multiple Criteria

Dec 4, 2009

I have a file with telemetry data for n individuals. For each individual I have data on a number of days, over the course of a couple of years. On any given day, I have from 1 to several "fixes" for an individual. Fixes range from 1 to 3, with 3 being the "best". Note that I do not necessarily have data for all individuals on a given day. What I want to do is extract the row with best fix for each individual, on each day for which I have data for that individual. If there is more than one best fix for an individual on a given day, then I want to choose the first best fix (temporally). Right now my data is set up with columns:

INDV DATE TIME FIX ....other attributes

INDV = unique code identifying the individual

The data is sorted by INDV, then by DATE, then by TIME.

I figured I could do this using nested IF statements, where "TRUE" would be added to a new column (e.g., HIGHFIX) in the row of the first highest fix on each day for each individual, but have not been successful in doing this. I've also tried conditional formatting. Again, no success here.

View 3 Replies View Related

Conditional Formatting - Highlight Row Based On Cell Criteria

May 19, 2013

I need a macro that will highlight a row based on a cell criteria. I have used conditional formatting but there is a lot to format and has dramatically slowed the sheet down. However, I know nothing about macros/vbas.

E.g. If it states JI sent then I want the whole row to be highlighted light green.

App form needed = peach
Waiting list = light blue
Cancelled = red font strikethrough
DNA = purple font strikethrough.

Also - it could well be that in the row it has App form received, JI Sent and DNA all in 1 go. So ideally I would want that row to be highlighted light green with purple font strikethrough.

Booking Status
JI Status
Date JI Sent
Attendance
Cancellation date
Chargable / non chargable Cancellations
Chargeable / non chargeable DNA
Date taken
Time taken
Event Title
Start Date
Time

App form received
JI Sent

[Code] ......

View 3 Replies View Related

Macro To Move Cell Down A Row Based On Criteria

Feb 20, 2009

I would like a simple macro that would actually move a cell based on criteria. In my case it would be: In row A, if a cell starts with 'Agent Name' then that cell needs to move down one cell replacing the contents of that cell.

View 3 Replies View Related

Cut Data Based Upon Criteria And Move It To New Worksheet

Jan 10, 2012

Error in the code, where I'm trying to copy the data (based on criteria) from one worksheet to another, then delete the data from the first worksheet. In the code (I have complied from this message board) I get an 1004 error "Application-defined or Object-defined error".

Code:
Option Explicit
Sub CopyALColKYes()
Dim NR As Long, c As Range, firstaddress As String
Application.ScreenUpdating = False
NR = Sheets("Completed-Expired").Cells(Rows.Count, 13).End(xlUp).Row + 1

[Code] .........

View 9 Replies View Related

Move Rows In A Table Based Upon Certain Criteria

Jan 3, 2014

I have four sheets with the same size tables and just need rows to be able to be moved back and forth based on input from a certain cell and just added to the next available blank cell in the desired table. Adding the code from the webpage below works very well but only puts into the worksheet and not into the actual table. Is there any way making it actually input the row into the table itself?

Creating Macro that automatically moves row to another spreadsheet?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
If UCase(Target.Value) = "COMPLETE" Then
Target.EntireRow.Copy Destination:=Sheets("Archive"). _
Range("A" & Rows.Count).End(xlUp).Offset(1)
Target.EntireRow.Delete
End If
End If
End Sub

View 1 Replies View Related

Move Data Based On Criteria In Column

May 2, 2007

Here is the situation:

There is a spreadsheet that I use that has multiple plans in one column for various members. You can see the plan for Robert James and Amanda James is plan 5. If all this information was in column A how would you move the plan type say column C for each member?

For example
Column A
Customer Name
Robert James
Amanda James
Plan 5
Nikki Martinez
James Gross
Plan 6

View 4 Replies View Related

Highlight Cells Based On Deadline To ETA

Mar 28, 2014

I hope this can be done through conditional formatting because I am not familiar with macros.

Column A - order #
Column B - shipping method (this will only be air, sea, or blank; I get this through VLookup)
Column C - order ETA

I want to highlight Column C..... if Column B=Air highlight if ETA is in 2 weeks or less; if Column B=Sea or is blank highlight if ETA is in 5 weeks or less.

View 14 Replies View Related

Macro To Highlight A Cell Based On Other Cells Value

Aug 31, 2012

I need a macro to highlight a cell based on other cells.

For example:-

1. I wants to highlight A3 in red if all the cells from I3 to Y3 are empty.
2. I wants to highlight A3 in yellow if I3 is filled and J3 is empty
3. I wants to highlight A3 in green if if I3 is filled and J3 is also filled.

Actually I am having a grid which shows all the components of watch like KIt, case, dial , strap,hands.

The A row is purchase order no and then I3 and J3 belongs to KIt means if a po is not then I3 and J3 are empty so A3 is high Lighted in REd and if a po is placed then we put supplier name in I3 and it changes the color to yellow and when a po is confirmed we put confirmation date in J3 so A3 turns to green.

So in this way we can see if any Po is not yet placed or not yet confirmed based on cell color.

View 9 Replies View Related

Highlight Cells Based On Row/Column Being Visible

Feb 25, 2008

I am attempting to have a cell highlighted if the adjacent cells are visible, but no highlighting when the cells are hidden. For example, Cell D1 is highlighted when cells A1:C1 are visible, but not when cells A1:C1 are hidden. (this is for the group/outline feature)

View 3 Replies View Related

Highlight Matching Cells Based On Manual Text?

Mar 9, 2013

I have a list of names in Column B (Starting at B5) with assignments to them in Column A. I want the people who receive the file, to enter their name in B1 exactly as it appears multiple times in sheet. And hope to use conditional formatting to highlight (change the back ground color) of each cell their name appears in.

I've used a number of formulas in the Conditional formatting including "=(ISNUMBER(MATCH($B5:$B100,$B$1,0)))", Countif's and "Not(isnumber)..." but can't find a formula that picks up the whole text.

View 3 Replies View Related

Highlight Cells Based On Cell Reference On Another Worksheet

May 27, 2014

I am trying to find a way to automatically highlight all the cells specified by the cell references in a column on another worksheet (which will constantly have new values added).

On the attached sample, the 'Data Before' tab shows the base data before any highlighting. As cell references are added to the 'References' tab in 'column A' I want the respective cell to be highlighted on the 'Data Before' tab resulting in the 'Data After' tab and each time a new cell reference is added to the 'References' tab, the respective cell is then highlighted on the 'Data Before' tab.

I would like to have control over the highlight colour so that a different highlight colour can be specified for each date change, i.e. all cell references that are shown on the same date are the same colour but where the date changes a different highlight colour can be specified.

Highlight by Cell Reference.xlsx‎

View 9 Replies View Related

VBA To Highlight Cells Downward Based On Column A Number

Aug 26, 2013

I would like to highlight the cells in Column A the have numbers in them. Starting with that number shift down based on the number that many rows and highlight them.

HTMLSheet1 *AB154020842*4020843*4020844*4020845*4020846*4020847*40208483
5010909*50109010*50109011*50109012*50109013*50109014*501090153
50112016*50112017*50112018*50112019*50112020*50112021*501120222
50206523*50206524*50206525*50206526*50206527*50206528*50206529450212430*50212431*50212432*502124

View 3 Replies View Related

Move Rows Based On Content Of 2 Cells

Oct 24, 2006

At the moment I have 1 spreadsheet with 12 worksheets that I have to enter data into. I would rather enter all the data onto one sheet then have a macro move the data into the correct worksheet based on the contents of columns A and B.

The raw data will be entered into the worksheet "Data". Once complete, I would like the user to press a button and the macro to then identify from column A the suppliers name. If it is not one of the recognised suppliers, then it would move the row into the worksheet "one off". If it does recognise the supplier then it checks column B to see which of the two supplier's two worksheets it needs to copy it to, with the data entered into the correct column based on the column title (ie only the white columns).

View 9 Replies View Related

Conditional Formating: Highlight Cells Based On Text Of Another Cell

Aug 28, 2009

I have created a drop down box that allows me to choose multiple different Text Options. Example: Cell B4 has dropdown that lets me choose the Text Options, "House", "Car" and "Truck".

Next, I have 5 or so other columns underneath with their own text. What I want to do is have different cells highlight themselves depending on what is in Cell B4.

Example, if "House" is selected in B4, I want cells B7 and B9 to highlight. If "Car" is selected, I want cells B7, B8, and B10 to highlight. If "Truck" is highlighted, I want cells B9-B11 to highlight. Is this possible to do? I've tried using If/Then statements, but those don't seem to work for this type of thing.

View 3 Replies View Related

Conditional Formating: Highlight Cells Based On Multiple Stipulations

Sep 14, 2009

I'm trying to get it so that a cell becomes automatically highlighted if 2 other cells conditions are met. For example, I want cell A5 to become highlighted if BOTH cell B1=Loan 1 AND cell B5=Yes. Here is a mockup of what I'm talking about incase it makes it easier to understand. I'm using Excel 2007.

View 3 Replies View Related

Using Conditional Formatting To Highlight Cells Based On Approaching Dates

May 25, 2013

I have a sheet where i need to highlight cells C3:C140 based on approaching dates. The cells C3:C140 contain dates generated from another file and would be random approaching dates.

The conditions are

1) Approaching Weekends (Sat and Sun) = always turn red
2) 3 days after today = orange
3) 2 days after today = dark orange
4) Other days = light orange

View 11 Replies View Related

Highlight Row And Column On Move Down Of Cell

Jun 18, 2009

As I move down a cell, I would like the entire row and column be highlighted. Is that possible?

View 11 Replies View Related

Highlight A Row And Move To The Bottom Of Spreadsheet

Nov 12, 2012

I'm working my way through a list of companies on an excel spreadsheet and making some notes.

After i've spoken to them its a simple keep them on the sheet in the current position or highlight the row red and move to the bottom or when they become a customer highlight green and move to the bottom.

I have a coloumn called customer (J) is it possible if i put a Y in that cell to make the row green and move to the bottom and also if i put a N in the column make the row green and move to bottom.

View 8 Replies View Related

How To Move Data In Various Cells To New Columns Based On Search On Primary Column

Feb 4, 2014

I have a sheet which I need to arrange and it looks like -

Column A
Column B
Column C
Column D

[Code]...

the values in Column B, C , D , E & F need to be in new columns based on what we have in Column A associated with "1"

Output should be -
Column A
Column B
Column C

[Code]....

so whatever is in Column A associated with "0" should remain in place whereas whatever is in Colimn A associated with "1" should get added to the new columns. 0 and 1 resembles the 1 Records which are used as a split to normalize the data.

View 9 Replies View Related

Fill Cells Based On Corresponding Cells Matching 3 Criteria

Jul 1, 2008

I am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.

I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.

I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.

View 8 Replies View Related

Highlight Cells In Sheet 1 Based On Cells Existing In Sheet 2

Jul 11, 2013

I have a sheet (sheet1) of around 900 rows with data in columns A-K. Column B contains a unique value.

Sheet2 contains data which needs to be highlighted in sheet1 and each row in identified by a unique value in Column B.

sheet1.PNG
sheet2.png

In sheet 1, I only want to highlight the cell which exists in sheet 2 for the matching reference number in column B. For example, in sheet2, the value 'PM328491CN' exists and the value 'CN' is present in cell c2. I want the cell that contains this value in sheet 1 to be highlighted (cell C6).

So, find the matching reference, the highlight the cell in the row which is present in both sheets.

View 1 Replies View Related

How To Copy Cells Based Upon Criteria

Dec 22, 2009

Here's what we need to do:

We are compiling one master spreedsheet from many smaller ones and I need to figure out how to copy them quickly and efficiently. For example:

Small Spreadsheet:

Column B is marked as P, M, C or S. This represents the type of the account.
Column C is the name of the account.

Master Spreadsheet:

We have a column for each account type (P,M,C,S)

Can we use a formula/macro to automatically copy columns B and C from the small sheet and organize them accordingly in the Master Spreadsheet?

For example:

Small spreadsheet:

P Account Name1
M Account Name 2
M Account Name3
C Account Name4
M Account Name5

would appear in the Master Spreadsheet as:

P M C
Account Name1 Account Name2
Account Name1 Account Name3 Account Name4
Account Name1 Account Name5

View 10 Replies View Related

VBA To Fill In Cells Based On Criteria

Apr 4, 2009

I just stumbled onto this forum while Googling around, and it looks great. I'm pretty new to VBA coding so please bear with me. Effectively what I'm looking to do in this mock-up file, is anywhere the currency is "EUR" in column A, I'd like the relative cell in Column G to change to "INSERT TEXT", else I want it to untouched (i.e. to keep the comment it currently has).

View 6 Replies View Related

Copy Cells Based On Criteria

Jan 29, 2010

I have data like in excel, which I uploaded and I need "simple" thing, If in column G is text Australia then copy from D column each cell which has "AUS1*" + copy the same number cell from C column and copy it into cell A and B. I hope I wrote it understandable

View 2 Replies View Related

VBA Copy Row Based Upon 2 Cells With 3 Criteria

Aug 29, 2012

I'm not sure why this doesn't work on the dummy data?

Problems:It doesn't recognize the September 2011 datesIt is supposed to pull dates between July and August (i.e. 2 dates) and pull everything except those dates.

The date strings have to be variable based since it can't be hard coded.

Code:
Sub copyrow()
Dim rc As Integer, row As Integer, i As Integer
Dim mm As String, fdt As String, pdt As String, mo As String, yr As String
Dim Date1 As String, Date2 As String
mm = Month(Date) - 1
mo = Format(Now(), "mm") 'ex. "08"
yr = Format(Now(), "yyyy")
Date1 = mm & "/01/" & yr
Date2 = mo & "/01/" & yr

[code].....

View 2 Replies View Related

Sum Range Of Cells Based On Criteria?

Sep 27, 2013

I have the following data

Country
Aug-13
Sep-13
Oct-13
Nov-13

UK
3
5
3
2

USA
10
14
16
13

France
1
5
7
9

What I would like to do is sum a specific range of this data based on three criteria. Each criteria is in its own cell.

- Start Date
- End Date
- Country

So if I wanted to sum from 'Sep-13' to 'Nov-13' for 'USA', the formula would give me the answer 43.

View 4 Replies View Related

Sum Cells Based On Row And Column Criteria

Apr 30, 2014

I am really struggling to summarise weekly budget data into period budget data by subcategory.

I have column headings A3 being Subcategory B3 being GL Code & C3 being GL Name - where column B & C are distinct values but column A are not. And then in D2 I have the period no e.g. P01, with the Week no in D3. I want to sum all cells that are in a particular subcategory and period - the data set is broken down by weeks.

View 4 Replies View Related

Locking Cells Based On Criteria

Jun 28, 2006

I have a query in relation to locking cells to disallow editing based on a dynamic range. The VBA code I have thus far is this :

Sub PasteForecastWO()
rowarr = Array(10, 26, 28, 69, 72, 79, 81, 87, 89, 104, 106, 114, 116, 122)
For Each ce In Range("S8:AD8")
If ce = "ACTUAL" Then
For i = LBound(rowarr) To UBound(rowarr) Step 2
Range(Cells(rowarr(i), ce.Column), Cells(rowarr(i + 1), ce.Column)).Value = Range(Cells(rowarr(i), ce.Column), Cells(rowarr(i + 1), ce.Column)).Value
Next i
End If
Next ce
End Sub

What I would like to do is lock the range that applies as the variable "ACTUAL" moves or changes along the range S8:AD8. I have attached the relevant sheet so that you may get a clearer picture.

View 7 Replies View Related

Mandatory Cells Based On Criteria

Dec 17, 2007

I need a macro that will check that the cells B1,C1,D1,E1 and one of the cells F1,G1,H1,I1 are filed out upon entry in A1.

If Not IsEmpty(.Range("A1")) Then
If WorksheetFunction. CountA(.Range("B1,C1,D1,E1")) <> 4 Then
MsgBox "All cells in row 1 are not filled out"
Application.Goto .Range("A1:I1")
Exit Sub

But how do I get it to also check that either F1 or G1 or H1 or I1 is also filled out. If possible I also would like it to beimpossible to add text in more then one of the cells F1:I1. Summary, upon entry in A1 I want cells B1:E1 plus one of the cells in F1:I1 to be mandatory, so in total there will be 5 mandaory cells.

View 2 Replies View Related

Merge Two Cells Based On Criteria

Dec 19, 2007

The begining portion of this works fine, I have it moveing data from one column to another, but I then need it to combine two rows (BW and P) and then place that into column (O)...

View 3 Replies View Related

Delete Certain Cells Within Row Or Column Based On Criteria?

Nov 7, 2013

What i really need it to do is if each row in column H = "Cleared" then to only clear columns A, C to H. Theres a formula in B that needs to be left... any ideas?

Also i need this to work on all atbs as the commandbutton will on a master tab

VB:
Private Sub CommandButton1_Click()
With ActiveSheet
.AutoFilterMode = False

[Code]....

View 4 Replies View Related

Count Cells In Row Based On Criteria In Column & Row As Well

Mar 10, 2014

count cells row wise that meets criteria both in column & Row as well.

View 2 Replies View Related

Can A Formula Only Occupy Certain Cells Based On Criteria

Dec 8, 2009

Im currently using a formula in my report to track training completed by employees. The formula looks for the employee number in the report and the course name and marks Y to say if completed or N to say if not completed.

The level of the employees dictates what courses they need to complete i.e if its a store manager they need to complete all courses where as a bronze employee only needs to complete the first three courses.

At the moment i drag the formula up to the course they need to complete. I would like it if i could add something to the existing formula that looks at their level and then automatically only populates the formula up to the courses they are required to do.

Its very hard to explain so i have added an example:

View 8 Replies View Related

Open File Based Cells Criteria?

Jun 29, 2014

How to open files based criteria in cells value?

[Code] .....

View 5 Replies View Related

VBA To Populate To Specific Cells Based On Criteria

Jul 6, 2014

I am looking for a VBA that could populate from column (A2:A5) to cells (B2:F5) as in the attached spreadsheet, based on the frequency, in a five year time-frame.

screen_print.JPG

[Code] .....

View 1 Replies View Related

Run / Loop Through Cells And Delete Based On Criteria

Mar 4, 2014

I am trying to run (or loop) through a column of cells, and if cell contains certain text (e.g. ALPHA, BRAVO, CHARLIE) and delete the row if found.

My macro as I run it:

Sub DATA_TEXT ()

'Number of cells to loop through (I am unsure how the .xlend works!)
For i = 1 to 2500

'If function defining the criteria.
If cells (1, i) = "ALPHA" OR "BRAVO" OR "CHARLIE" Then

[Code] .....

View 4 Replies View Related

ADVERTISEMENT