Formatting a cell and moving data to a new sheet?

[daveray]

I have a sheet that calculates revenue from sales and the insurance associated with the equipment. I want to add some simple code that will format the sale value in Red type thus highlighting sales that that are sold without insurance. I would then like that sale amount together with the details of the client, salesman, sale value and date to be copied onto the third sheet in the workbook called 'No Insurance'

Column A Salesman
Cloumn B Value
Column C Client
Column D Insurance value
Coulmn E % Insurance
Cloumn F Date of sale

All help would be welcomed

Dave
This message was edited by daveray on 2002-09-27 02:00

 

[jimboy]

Try this...

Sub zero_insurance()

Sheets("All").Select
Range("D2").Select
Do Until ActiveCell = ""
If ActiveCell = "0" Then
Selection.Font.ColorIndex = 3
ActiveCell.Offset(0, 0).Rows("1:1").EntireRow.Select

Selection.Copy
Sheets("No Insurance").Select

Application.Goto Reference:="R65536C1"
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Sheets("All").Select
ActiveCell.Offset(1, 3).Select

Else
ActiveCell.Offset(1, 0).Range("A1").Activate
End If
Loop
End Sub

 

[daveray]

I tried the code but it didn't appear to work. I entered some trial data and it picked up on one of the examples. When I ran it again, it picked up the same example?

I need it to work constantly so that when the data is input the zero insurance is picked up.

Any ideas?

Dave

 

[daveray]

I tried the code but it didn't appear to work. I entered some trial data and it picked up on one of the examples. When I ran it again, it picked up the same example?

I need it to work constantly so that when the data is input the zero insurance is picked up.

Any ideas?

Dave

 

[daveray]

I tried the code but it didn't appear to work. I entered some trial data and it picked up on one of the examples. When I ran it again, it picked up the same example?

I need it to work constantly so that when the data is input the zero insurance is picked up.

Any ideas?

Dave

 

[daveray]

I tried the code but it didn't appear to work. I entered some trial data and it picked up on one of the examples. When I ran it again, it picked up the same example?

I need it to work constantly so that when the data is input the zero insurance is picked up.

Any ideas?

Dave

 

[daveray]

I tried the code but it didn't appear to work. I entered some trial data and it picked up on one of the examples. When I ran it again, it picked up the same example?

I need it to work constantly so that when the data is input the zero insurance is picked up.

Any ideas?

Dave

 

[jimboy]

Sorry Dave, I've been off work, what part didn't work?


Can you email the workbook and explain?

*email removed*
This message was edited by jimboy on 2002-10-08 06:34