New sheet using conditional formatting [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hello,

I have a very basic table on Excel which I use to keep contract data. I'm currently using conditional formatting to highlight the row if the contract is out of date (i have a start date & end date in different columns)

I've looked on Help and using Google to find a way of creating a list on a new tab/spreadsheet, just so its easier to read.

Surely this must be possible?

Thanks

Daron

5 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
792
can you post a small extract of your sheet and rephrase your question giving examples
16/07/2009
Company Serial Rep Com Start End
BAA 643 RA Aug-04 PPM 01/04/2009 31/03/2010


Sorry, hopefully the above data will show.

They're basically a list of contracts, in a table of 100's, I've put in a formula so that the line highlights red if it expires the date in the top left. I wanted to know if all the data that dates dont meet the formula (highlighted red) could automatically paste onto the next page, so they're grouped together.

Kind of like an autofilter I think
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
792
it is difficult to copy your data it comes like this

Company/ Serial/ Rep/ Com/ start/ End/
BAA / 643 RA/ 4-Aug/ PPM/ 1/4/2009/ 31/03/2010/
the sign / separates the columns


is it ok . you have entered 16/07/2009 at the top. this is perhaps today's date. tomorrow it will become
17/07/2009. this may not be necessary.

do you mean to say the if end date crosses today's date this row should be in red and these rows should be moved to another sheet so that you can monitor. What is the 4-Aug.

when I said a small extract the data I meant 3 or 4 rows with fictitious data including dates. It is difficult to imagine what could be the sheet and think of helping you.
greetings
Ok, forget all the other stuff

Company / Contract Start / Contract End
ABC123 / 01.01.2009 / 31.12.2009
ABC789 / 01.06.2008 / 31.05.2009

As the bottom company ABC789 contract expired last month, this row would be highlighted red, as I set up a conditional format that will do this if it doesnt fall within todays date

I wanted to know if, at the same time this information was highlighted red, it could also be seen on a new spreadsheet page, a list of some kind.

It would also need to stay in its original position.
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
792
keep original file safely somewhere
as you have already colored the relevant row red I have made that line in the code unoperable
your main sheet is sheet 1. the expired date's data is copied in sheet 2
now try this macro
my version is excel 2002

Sub test()
Dim rng As Range, r As Range, r1
With Worksheets("sheet1")
Set rng = Range(.Range("c2"), .Range("c2").End(xlDown))
For Each r In rng
 r1 = Trim(r)
r1 = DateSerial(Right(r1, 4), Mid(r1, 4, 2), Left(r1, 2))
'MsgBox r1
If r1 > Date Then GoTo line1
'r.EntireRow.Interior.ColorIndex = 3
r.EntireRow.Copy

With Worksheets("sheet2")
.Cells(Rows.Count, "a").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End With
line1:
Next r
End With
Application.CutCopyMode = False
End Sub

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!