New sheet using conditional formatting

Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
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
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
can you post a small extract of your sheet and rephrase your question giving examples
0
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
0
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
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
0
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.
0
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
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
0