I have a worksheet which shows many rows of data from 7 vendors. Each of the
7 vendors are listed in columns. I want to sort the values of each cell in
each row. I want to see the highest value in one color proceeding in
different colors as the value decrease. For example in row 1, D1 may be
highest value thus is colored blue and A1 may be the lowest so it would be
red. On row 2 E2 might be the
highest thus would be colored blue, A2 might be next and is colored orange,
while B2 is the lowest and ends up being red. Then repeat this process for
all of the data rows. This way I can get a quick visual observation of the
vendor which has to most items in the lowest or highest category.

--
Thanks for the help

Abe : First sort by item and price. There should be 7 rows for each item.
Highlight the first seven rows wit a different color. Then copy these 7 row.
then highlight the rest of the worksheet. Use Paste Special and Paste only
the Format. the same 7 colors will repeat on every row. You can then sort
again by vendor.

"AbeAbeAbe" wrote:

I have a worksheet which shows many rows of data from 7 vendors. Each of the
7 vendors are listed in columns. I want to sort the values of each cell in
each row. I want to see the highest value in one color proceeding in
different colors as the value decrease. For example in row 1, D1 may be
highest value thus is colored blue and A1 may be the lowest so it would be
red. On row 2 E2 might be the
highest thus would be colored blue, A2 might be next and is colored orange,
while B2 is the lowest and ends up being red. Then repeat this process for
all of the data rows. This way I can get a quick visual observation of the
vendor which has to most items in the lowest or highest category.

--
Thanks for the help

Joel,

This is not what I am looking for. I do not what to sort the data by
rearangeing the data ina different order showing the highest to the lowest.
I want to sort to data only by making the cell with the higest value in a row
1 one color , the second highest value in row 2 a different color ect. I do
not what the data in the cells or rows to move. I want the data to stay
where it is, I just want the data to change colors.
--
Thanks for the help


"Joel" wrote:

Abe : First sort by item and price. There should be 7 rows for each item.
Highlight the first seven rows wit a different color. Then copy these 7 row.
then highlight the rest of the worksheet. Use Paste Special and Paste only
the Format. the same 7 colors will repeat on every row. You can then sort
again by vendor.

"AbeAbeAbe" wrote:

I have a worksheet which shows many rows of data from 7 vendors. Each of the
7 vendors are listed in columns. I want to sort the values of each cell in
each row. I want to see the highest value in one color proceeding in
different colors as the value decrease. For example in row 1, D1 may be
highest value thus is colored blue and A1 may be the lowest so it would be
red. On row 2 E2 might be the
highest thus would be colored blue, A2 might be next and is colored orange,
while B2 is the lowest and ends up being red. Then repeat this process for
all of the data rows. This way I can get a quick visual observation of the
vendor which has to most items in the lowest or highest category.

--
Thanks for the help

Anyone have any ideas?
--
Thanks for the help


"AbeAbeAbe" wrote:

Joel,

This is not what I am looking for. I do not what to sort the data by
rearangeing the data ina different order showing the highest to the lowest.
I want to sort to data only by making the cell with the higest value in a row
1 one color , the second highest value in row 2 a different color ect. I do
not what the data in the cells or rows to move. I want the data to stay
where it is, I just want the data to change colors.
--
Thanks for the help


"Joel" wrote:

Abe : First sort by item and price. There should be 7 rows for each item.
Highlight the first seven rows wit a different color. Then copy these 7 row.
then highlight the rest of the worksheet. Use Paste Special and Paste only
the Format. the same 7 colors will repeat on every row. You can then sort
again by vendor.

"AbeAbeAbe" wrote:

I have a worksheet which shows many rows of data from 7 vendors. Each of the
7 vendors are listed in columns. I want to sort the values of each cell in
each row. I want to see the highest value in one color proceeding in
different colors as the value decrease. For example in row 1, D1 may be
highest value thus is colored blue and A1 may be the lowest so it would be
red. On row 2 E2 might be the
highest thus would be colored blue, A2 might be next and is colored orange,
while B2 is the lowest and ends up being red. Then repeat this process for
all of the data rows. This way I can get a quick visual observation of the
vendor which has to most items in the lowest or highest category.

--
Thanks for the help

This has to be a macro. Give me a few minutes and I will write one.

"AbeAbeAbe" wrote:

Anyone have any ideas?
--
Thanks for the help


"AbeAbeAbe" wrote:

Joel,

This is not what I am looking for. I do not what to sort the data by
rearangeing the data ina different order showing the highest to the lowest.
I want to sort to data only by making the cell with the higest value in a row
1 one color , the second highest value in row 2 a different color ect. I do
not what the data in the cells or rows to move. I want the data to stay
where it is, I just want the data to change colors.
--
Thanks for the help


"Joel" wrote:

Abe : First sort by item and price. There should be 7 rows for each item.
Highlight the first seven rows wit a different color. Then copy these 7 row.
then highlight the rest of the worksheet. Use Paste Special and Paste only
the Format. the same 7 colors will repeat on every row. You can then sort
again by vendor.

"AbeAbeAbe" wrote:

I have a worksheet which shows many rows of data from 7 vendors. Each of the
7 vendors are listed in columns. I want to sort the values of each cell in
each row. I want to see the highest value in one color proceeding in
different colors as the value decrease. For example in row 1, D1 may be
highest value thus is colored blue and A1 may be the lowest so it would be
red. On row 2 E2 might be the
highest thus would be colored blue, A2 might be next and is colored orange,
while B2 is the lowest and ends up being red. Then repeat this process for
all of the data rows. This way I can get a quick visual observation of the
vendor which has to most items in the lowest or highest category.

--
Thanks for the help

Try this macro. I picked colors randomly. If you don't like these colors
use lRecord Macro feature and select your colors. then stop macro and see
the colors that are selected.


You need to change the 1st row, Last row, and first col to fit you
spreadsheet

Sub ColorColumn()

Const Firstrow = 5
Const LastRow = 12
Const FirstCol = "K"

Const MYPINK = 7
Const MYORANGE = 44
Const MYYELLOW = 6
Const MYGREEN = 4
Const MYBLUE = 8
Const MYBROWN = 54
Const MYGRAY = 15


Dim SortOrder(7, 2)
Dim ColorIndex(7)

ColorIndex(1) = MYPINK
ColorIndex(2) = MYORANGE
ColorIndex(3) = MYYELLOW
ColorIndex(4) = MYGREEN
ColorIndex(5) = MYBLUE
ColorIndex(6) = MYBROWN
ColorIndex(7) = MYGRAY



RowCount = Firstrow
Do While RowCount <= LastRow

'get data from worksheet
For ColumnCount = 1 To 7
SortOrder(ColumnCount, 1) = ColumnCount
SortOrder(ColumnCount, 2) = Range(FirstCol + Mid(Str(RowCount), 2)). _
Offset(rowOffset:=0, columnOffset:=ColumnCount - 1).Value

Next ColumnCount

'sort numbers
For i = 1 To 6
For j = (i + 1) To 7

If SortOrder(i, 2) SortOrder(j, 2) Then

Temp = SortOrder(j, 2)
SortOrder(j, 2) = SortOrder(i, 2)
SortOrder(i, 2) = Temp

Temp = SortOrder(j, 1)
SortOrder(j, 1) = SortOrder(i, 1)
SortOrder(i, 1) = Temp

End If

Next j
Next i

'change colors
For ColumnCount = 1 To 7
With Range(FirstCol + Mid(Str(RowCount), 2)). _
Offset(rowOffset:=0, columnOffset:=ColumnCount - 1).Interior

.ColorIndex = ColorIndex(ColumnCount)
.Pattern = xlSolid
End With

Next ColumnCount


RowCount = RowCount + 1


Loop


End Sub

"AbeAbeAbe" wrote:

I have a worksheet which shows many rows of data from 7 vendors. Each of the
7 vendors are listed in columns. I want to sort the values of each cell in
each row. I want to see the highest value in one color proceeding in
different colors as the value decrease. For example in row 1, D1 may be
highest value thus is colored blue and A1 may be the lowest so it would be
red. On row 2 E2 might be the
highest thus would be colored blue, A2 might be next and is colored orange,
while B2 is the lowest and ends up being red. Then repeat this process for
all of the data rows. This way I can get a quick visual observation of the
vendor which has to most items in the lowest or highest category.

--
Thanks for the help

Joel,

I am humbled that you took the time to write this macro. I really
appreciate it. HOwever I have never had any experience with macro's before.
I have tried to implement this for a few hours and can not get it to work.
Could I email you my file and see if you can get the macro applied to it?

Thanks again for you help!
--
Thanks for the help


"Joel" wrote:

Try this macro. I picked colors randomly. If you don't like these colors
use lRecord Macro feature and select your colors. then stop macro and see
the colors that are selected.


You need to change the 1st row, Last row, and first col to fit you
spreadsheet

Sub ColorColumn()

Const Firstrow = 5
Const LastRow = 12
Const FirstCol = "K"

Const MYPINK = 7
Const MYORANGE = 44
Const MYYELLOW = 6
Const MYGREEN = 4
Const MYBLUE = 8
Const MYBROWN = 54
Const MYGRAY = 15


Dim SortOrder(7, 2)
Dim ColorIndex(7)

ColorIndex(1) = MYPINK
ColorIndex(2) = MYORANGE
ColorIndex(3) = MYYELLOW
ColorIndex(4) = MYGREEN
ColorIndex(5) = MYBLUE
ColorIndex(6) = MYBROWN
ColorIndex(7) = MYGRAY



RowCount = Firstrow
Do While RowCount <= LastRow

'get data from worksheet
For ColumnCount = 1 To 7
SortOrder(ColumnCount, 1) = ColumnCount
SortOrder(ColumnCount, 2) = Range(FirstCol + Mid(Str(RowCount), 2)). _
Offset(rowOffset:=0, columnOffset:=ColumnCount - 1).Value

Next ColumnCount

'sort numbers
For i = 1 To 6
For j = (i + 1) To 7

If SortOrder(i, 2) SortOrder(j, 2) Then

Temp = SortOrder(j, 2)
SortOrder(j, 2) = SortOrder(i, 2)
SortOrder(i, 2) = Temp

Temp = SortOrder(j, 1)
SortOrder(j, 1) = SortOrder(i, 1)
SortOrder(i, 1) = Temp

End If

Next j
Next i

'change colors
For ColumnCount = 1 To 7
With Range(FirstCol + Mid(Str(RowCount), 2)). _
Offset(rowOffset:=0, columnOffset:=ColumnCount - 1).Interior

.ColorIndex = ColorIndex(ColumnCount)
.Pattern = xlSolid
End With

Next ColumnCount


RowCount = RowCount + 1


Loop


End Sub

"AbeAbeAbe" wrote:

I have a worksheet which shows many rows of data from 7 vendors. Each of the
7 vendors are listed in columns. I want to sort the values of each cell in
each row. I want to see the highest value in one color proceeding in
different colors as the value decrease. For example in row 1, D1 may be
highest value thus is colored blue and A1 may be the lowest so it would be
red. On row 2 E2 might be the
highest thus would be colored blue, A2 might be next and is colored orange,
while B2 is the lowest and ends up being red. Then repeat this process for
all of the data rows. This way I can get a quick visual observation of the
vendor which has to most items in the lowest or highest category.

--
Thanks for the help

"AbeAbeAbe" wrote:

Joel,

I am humbled that you took the time to write this macro. I really
appreciate it. HOwever I have never had any experience with macro's before.
I have tried to implement this for a few hours and can not get it to work.
Could I email you my file and see if you can get the macro applied to it?

Thanks again for you help!
--
Thanks for the help


"Joel" wrote:

Try this macro. I picked colors randomly. If you don't like these colors
use lRecord Macro feature and select your colors. then stop macro and see
the colors that are selected.


You need to change the 1st row, Last row, and first col to fit you
spreadsheet

Sub ColorColumn()

Const Firstrow = 5
Const LastRow = 12
Const FirstCol = "K"

Const MYPINK = 7
Const MYORANGE = 44
Const MYYELLOW = 6
Const MYGREEN = 4
Const MYBLUE = 8
Const MYBROWN = 54
Const MYGRAY = 15


Dim SortOrder(7, 2)
Dim ColorIndex(7)

ColorIndex(1) = MYPINK
ColorIndex(2) = MYORANGE
ColorIndex(3) = MYYELLOW
ColorIndex(4) = MYGREEN
ColorIndex(5) = MYBLUE
ColorIndex(6) = MYBROWN
ColorIndex(7) = MYGRAY



RowCount = Firstrow
Do While RowCount <= LastRow

'get data from worksheet
For ColumnCount = 1 To 7
SortOrder(ColumnCount, 1) = ColumnCount
SortOrder(ColumnCount, 2) = Range(FirstCol + Mid(Str(RowCount), 2)). _
Offset(rowOffset:=0, columnOffset:=ColumnCount - 1).Value

Next ColumnCount

'sort numbers
For i = 1 To 6
For j = (i + 1) To 7

If SortOrder(i, 2) SortOrder(j, 2) Then

Temp = SortOrder(j, 2)
SortOrder(j, 2) = SortOrder(i, 2)
SortOrder(i, 2) = Temp

Temp = SortOrder(j, 1)
SortOrder(j, 1) = SortOrder(i, 1)
SortOrder(i, 1) = Temp

End If

Next j
Next i

'change colors
For ColumnCount = 1 To 7
With Range(FirstCol + Mid(Str(RowCount), 2)). _
Offset(rowOffset:=0, columnOffset:=ColumnCount - 1).Interior

.ColorIndex = ColorIndex(ColumnCount)
.Pattern = xlSolid
End With

Next ColumnCount


RowCount = RowCount + 1


Loop


End Sub

"AbeAbeAbe" wrote:

I have a worksheet which shows many rows of data from 7 vendors. Each of the
7 vendors are listed in columns. I want to sort the values of each cell in
each row. I want to see the highest value in one color proceeding in
different colors as the value decrease. For example in row 1, D1 may be
highest value thus is colored blue and A1 may be the lowest so it would be
red. On row 2 E2 might be the
highest thus would be colored blue, A2 might be next and is colored orange,
while B2 is the lowest and ends up being red. Then repeat this process for
all of the data rows. This way I can get a quick visual observation of the
vendor which has to most items in the lowest or highest category.

--
Thanks for the help