=IF(U30,U1,"")

what i really want is for this to look in u4 and then u5 and so on until it
finds a 1 or a (1) and if it finds the negative (1) turn the reported number
red.

then in the next adjacent cell in the the formula to look for the next cell
that contains a number so if the first one found a number in u4 then i would
need this one to ignor that and look at u5 and so on.

i tried to use LARGE which i think is right but i'm doing something wrong. i
only need to find the first 6 columns that have a 1 in them because there
would never be more than 6 columns that contain a 1.

i will be searching about 40 columns.

You might be better with a macro: ALT + F11, Insert, Module and copy the code
into the module.

Return to the sheet, ALT + F8, select the macro and click run.

Sub FormatOnes()
Dim c, x As Integer, y As Long
Dim rngdata As Range
' change the change to suit
Set rngdata = Range("U3:U20")

For Each c In rngdata
y = c.Row
x = c.Column
If c = 1 Or c = -1 Then
Cells(y, x).Font.ColorIndex = 3
If Cells(y - 1, x) = c Then
Cells(y, x).Font.ColorIndex = 0
End If
End If
Next
End Sub


Regards
Peter

"Tomkat743" wrote:

=IF(U30,U1,"")

what i really want is for this to look in u4 and then u5 and so on until it
finds a 1 or a (1) and if it finds the negative (1) turn the reported number
red.

then in the next adjacent cell in the the formula to look for the next cell
that contains a number so if the first one found a number in u4 then i would
need this one to ignor that and look at u5 and so on.

i tried to use LARGE which i think is right but i'm doing something wrong. i
only need to find the first 6 columns that have a 1 in them because there
would never be more than 6 columns that contain a 1.

i will be searching about 40 columns.

SORRY,

I worte the question wrong. what i am trying to do is have it look in cell
u4 then cell v4 then cell w4 and so on, find a 1 or possibly a -1 and then
report the data in cell v1 to cell n4.

Each row has data from u to cs with about 500 rows of data. I have to parse
each row looking for a 1 or -1 or sometimes even a 2 or -2 then report the
value of the header row to one of 6 blank columns n to s.

"Billy Liddel" wrote:

You might be better with a macro: ALT + F11, Insert, Module and copy the code
into the module.

Return to the sheet, ALT + F8, select the macro and click run.

Sub FormatOnes()
Dim c, x As Integer, y As Long
Dim rngdata As Range
' change the change to suit
Set rngdata = Range("U3:U20")

For Each c In rngdata
y = c.Row
x = c.Column
If c = 1 Or c = -1 Then
Cells(y, x).Font.ColorIndex = 3
If Cells(y - 1, x) = c Then
Cells(y, x).Font.ColorIndex = 0
End If
End If
Next
End Sub


Regards
Peter

"Tomkat743" wrote:

=IF(U30,U1,"")

what i really want is for this to look in u4 and then u5 and so on until it
finds a 1 or a (1) and if it finds the negative (1) turn the reported number
red.

then in the next adjacent cell in the the formula to look for the next cell
that contains a number so if the first one found a number in u4 then i would
need this one to ignor that and look at u5 and so on.

i tried to use LARGE which i think is right but i'm doing something wrong. i
only need to find the first 6 columns that have a 1 in them because there
would never be more than 6 columns that contain a 1.

i will be searching about 40 columns.