Search : in
By :

New sheet using conditional formatting

Last answer on Jul 17, 2009 1:52:10 am BST Daron, on Jul 15, 2009 1:57:50 pm BST 
 Report this message to moderators

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

Configuration: Windows XP Internet Explorer 7.0

Best answers for « New sheet using conditional formatting » in :
Unlimited Conditional Formatting Show Unlimited Conditional Formatting Introduction Principle Code Introduction The limitation of MFC (Conditional Formatting) to Excel version 2007 is less than 3. This is very short. Having seen many applications to push the limit, I am...
Conditional formating - apply on more cells Show
Remove blank cell from conditional formatting Show
Colouring cells on conditions ShowColouring cells on conditions There are many pratical functions under Excel which is not commonly used. Example: If you wish a cell automatically turns red (or other formatting border, frame etc) under one condition: a result, a...
Avoid duplicates in Excel ShowAvoid duplicates in Excel In order to avoid duplication when encoding in a column from an excel sheet: take the conditional format on the first cell under the headings (eg A2) choose the following formula:...
New Hard drive installed – what to be done? ShowNew Hard drive installed – what to be done? Initializing the HDD Creating new partitions and formatting First You bought a new hard drive but it doesn’t appear in My Computer? It needs to be initialized and formatted for...
Download Any Audio Video Converter ShowWith all the new audio/video formats in the multimedia world, a beginner can get lost easily. Then to solve this problem, simply convert the found files into the formats which are familiar to you. Any Audio Video Converter is a powerful tool which...
Download CDBF Shell Show
Spreadsheets - Conditional expressions ShowWhat is a Conditional Structure? Conditional structures are instructions that allow to test if a condition is true or not. Conditional structures may be associated together. To successfully execute these tests using logical functions, spreadsheets...
UNIX system - The shell ShowIntroduction to the shell The command interpreter is the interface between the user and the operating system, hence its name "shell". The shell therefore acts as an intermediary between the operating system and the user using command lines...
CSS - Style sheets ShowIntroduction to style sheets The concept of style sheets first appeared in 1996 when the W3C published a new recommendation entitled "Cascading Style Sheets", or CSS for short. The principle behind style sheets involves using a single document to...
Ask your question Reply

1

venkat1926, on Jul 16, 2009 1:36:28 am BST

Can you post a small extract of your sheet and rephrase your question giving examples

   
Reply to venkat1926

2

Daron, on Jul 16, 2009 8:31:16 am BST

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

   
Reply to Daron

3

venkat1926, on Jul 16, 2009 10:24:39 am BST

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

   
Reply to venkat1926

4

Daron, on Jul 16, 2009 10:43:08 am BST

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.

   
Reply to Daron

5

 venkat1926, on Jul 17, 2009 1:52:10 am BST

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

   
Reply to venkat1926
Ask your question Reply
They need your help