New sheet using conditional formatting
Closed
Daron
-
Jul 15, 2009 at 08:57 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 16, 2009 at 08:52 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 16, 2009 at 08:52 PM
Related:
- New sheet using conditional formatting
- Windows network commands cheat sheet - Guide
- Google sheet right to left - Guide
- Mark sheet in excel - Guide
- Little alchemy cheat sheet - Guide
- How to open excel sheet in notepad++ - Guide
5 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 15, 2009 at 08:36 PM
Jul 15, 2009 at 08:36 PM
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
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
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 16, 2009 at 05:24 AM
Jul 16, 2009 at 05:24 AM
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
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.
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.
Didn't find the answer you are looking for?
Ask a question
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 16, 2009 at 08:52 PM
Jul 16, 2009 at 08:52 PM
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
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
