Change cell background color based on content that results from li

This could be very simple, but I will lay the groundwork first. I have a
schedule spreadsheet that I import data to from a web based program. There
are existing filters to remove all formatting of the data and remove that
which we do not use. What remains is a non formatted sheet that other
workbooks link to so we can produce daily sheets. Now the data that is
linked on the other sheets may be for example the number 150. It appears
throughout the sheet and I would like to color any cell that contains the
number 150. The problem is I can't search for 150 because it really doesn't
exist. All that exists is the linked cell refernece to where this page gets
the data from.

The action I want to learn to produce is that, based on the resulting data,
color the cell(s) the chosen color. Something to the effect of if any cell
in workbook1 contains number 150, resulting from a link to a cell in
workbooka1, to be colored blue. This makes every occurance of 150 in a cell
change the cell background blue. I would like to have this formulated for
about 25 conditions.

I am willing to do all the work, all the training, all the coding, whatever
I need to so that I can make this work as we use this weekly and have to
manually alter background colors. I don't want to buy something to do it
since once I establish this formatting code it will not change very often if
at all.

Thank you in advance for any guidance and help you might provide!

For that many conditions, you'll want to use a macro most likely. Since this
will be imported data, I'll assume you'll run this macro after the import.
First, here's the macro you can use:

'================
Sub FormatColors()
For Each c In ActiveSheet.UsedRange

xCheck = c.Value
With c.Interior
Select Case xCheck

'Each case represents the value to look for
'and the ColorIndex corresponds to the color
'you want the background to be
Case 100
..ColorIndex = 6
Case 150
..ColorIndex = 9
'Repeat as necessary....

Case Else
'If not a previous condition, no fill
..ColorIndex = 0
End Select
End With
Next
End Sub
'================

'To create a key showing which number corresponds to which color
'you can run this quick macro, which uses row number to represent color
'Run this macro on a blank sheet

Sub CreateKey()
For i = 1 to 56
Cells(i,1).Interior.ColorIndex = i
Next
End Sub

--
Best Regards,

Luke M
"joemc911" wrote in message
...
This could be very simple, but I will lay the groundwork first. I have a
schedule spreadsheet that I import data to from a web based program.
There
are existing filters to remove all formatting of the data and remove that
which we do not use. What remains is a non formatted sheet that other
workbooks link to so we can produce daily sheets. Now the data that is
linked on the other sheets may be for example the number 150. It appears
throughout the sheet and I would like to color any cell that contains the
number 150. The problem is I can't search for 150 because it really
doesn't
exist. All that exists is the linked cell refernece to where this page
gets
the data from.

The action I want to learn to produce is that, based on the resulting
data,
color the cell(s) the chosen color. Something to the effect of if any
cell
in workbook1 contains number 150, resulting from a link to a cell in
workbooka1, to be colored blue. This makes every occurance of 150 in a
cell
change the cell background blue. I would like to have this formulated for
about 25 conditions.

I am willing to do all the work, all the training, all the coding,
whatever
I need to so that I can make this work as we use this weekly and have to
manually alter background colors. I don't want to buy something to do it
since once I establish this formatting code it will not change very often
if
at all.

Thank you in advance for any guidance and help you might provide!

Luke,
Would you be so kind to send me your E-Mail address to:
micky-a at tapuz.co.il
Thanks, Micky


"Luke M" wrote:

For that many conditions, you'll want to use a macro most likely. Since this
will be imported data, I'll assume you'll run this macro after the import.
First, here's the macro you can use:

'================
Sub FormatColors()
For Each c In ActiveSheet.UsedRange

xCheck = c.Value
With c.Interior
Select Case xCheck

'Each case represents the value to look for
'and the ColorIndex corresponds to the color
'you want the background to be
Case 100
..ColorIndex = 6
Case 150
..ColorIndex = 9
'Repeat as necessary....

Case Else
'If not a previous condition, no fill
..ColorIndex = 0
End Select
End With
Next
End Sub
'================

'To create a key showing which number corresponds to which color
'you can run this quick macro, which uses row number to represent color
'Run this macro on a blank sheet

Sub CreateKey()
For i = 1 to 56
Cells(i,1).Interior.ColorIndex = i
Next
End Sub

--
Best Regards,

Luke M
"joemc911" wrote in message
...
This could be very simple, but I will lay the groundwork first. I have a
schedule spreadsheet that I import data to from a web based program.
There
are existing filters to remove all formatting of the data and remove that
which we do not use. What remains is a non formatted sheet that other
workbooks link to so we can produce daily sheets. Now the data that is
linked on the other sheets may be for example the number 150. It appears
throughout the sheet and I would like to color any cell that contains the
number 150. The problem is I can't search for 150 because it really
doesn't
exist. All that exists is the linked cell refernece to where this page
gets
the data from.

The action I want to learn to produce is that, based on the resulting
data,
color the cell(s) the chosen color. Something to the effect of if any
cell
in workbook1 contains number 150, resulting from a link to a cell in
workbooka1, to be colored blue. This makes every occurance of 150 in a
cell
change the cell background blue. I would like to have this formulated for
about 25 conditions.

I am willing to do all the work, all the training, all the coding,
whatever
I need to so that I can make this work as we use this weekly and have to
manually alter background colors. I don't want to buy something to do it
since once I establish this formatting code it will not change very often
if
at all.

Thank you in advance for any guidance and help you might provide!


.

Thank you so much for the answer Luke! It appears right in line with what I
thought. May I make one more request, though. While I understand what you
are saying to do I don't know exactly how to do it. I don't know how to
create and run the macro nor how to use the macro to give me a color key
page. I am imagining I have to paste the code you provided into VB somehow.
I just don't have any real training or idea how to do this. I could use
pointers on the color key page as well, but am guessing this might just be
transparent once I learn the other part.

In any case, thanks again for such quick help! I'm lloking forward to being
able to put it in to practice!

Joe M

"Luke M" wrote:

For that many conditions, you'll want to use a macro most likely. Since this
will be imported data, I'll assume you'll run this macro after the import.
First, here's the macro you can use:

'================
Sub FormatColors()
For Each c In ActiveSheet.UsedRange

xCheck = c.Value
With c.Interior
Select Case xCheck

'Each case represents the value to look for
'and the ColorIndex corresponds to the color
'you want the background to be
Case 100
..ColorIndex = 6
Case 150
..ColorIndex = 9
'Repeat as necessary....

Case Else
'If not a previous condition, no fill
..ColorIndex = 0
End Select
End With
Next
End Sub
'================

'To create a key showing which number corresponds to which color
'you can run this quick macro, which uses row number to represent color
'Run this macro on a blank sheet

Sub CreateKey()
For i = 1 to 56
Cells(i,1).Interior.ColorIndex = i
Next
End Sub

--
Best Regards,

Luke M
"joemc911" wrote in message
...
This could be very simple, but I will lay the groundwork first. I have a
schedule spreadsheet that I import data to from a web based program.
There
are existing filters to remove all formatting of the data and remove that
which we do not use. What remains is a non formatted sheet that other
workbooks link to so we can produce daily sheets. Now the data that is
linked on the other sheets may be for example the number 150. It appears
throughout the sheet and I would like to color any cell that contains the
number 150. The problem is I can't search for 150 because it really
doesn't
exist. All that exists is the linked cell refernece to where this page
gets
the data from.

The action I want to learn to produce is that, based on the resulting
data,
color the cell(s) the chosen color. Something to the effect of if any
cell
in workbook1 contains number 150, resulting from a link to a cell in
workbooka1, to be colored blue. This makes every occurance of 150 in a
cell
change the cell background blue. I would like to have this formulated for
about 25 conditions.

I am willing to do all the work, all the training, all the coding,
whatever
I need to so that I can make this work as we use this weekly and have to
manually alter background colors. I don't want to buy something to do it
since once I establish this formatting code it will not change very often
if
at all.

Thank you in advance for any guidance and help you might provide!


.

Hi Joemc911
The link below is a sample worksheet with Luke M's macro. Slightly modified so
you can play with it.
http://cjoint.com/?dyplLsnupa
Let us know if that is really what you want.
HTH
John
P.S. I may not be able to help you with macros, I'm just starting to learn, but
someone else will, if your questions are clear with examples.

"joemc911" wrote in message
...
Thank you so much for the answer Luke! It appears right in line with what I
thought. May I make one more request, though. While I understand what you
are saying to do I don't know exactly how to do it. I don't know how to
create and run the macro nor how to use the macro to give me a color key
page. I am imagining I have to paste the code you provided into VB somehow.
I just don't have any real training or idea how to do this. I could use
pointers on the color key page as well, but am guessing this might just be
transparent once I learn the other part.

In any case, thanks again for such quick help! I'm lloking forward to being
able to put it in to practice!

Joe M

"Luke M" wrote:

For that many conditions, you'll want to use a macro most likely. Since this
will be imported data, I'll assume you'll run this macro after the import.
First, here's the macro you can use:

'================
Sub FormatColors()
For Each c In ActiveSheet.UsedRange

xCheck = c.Value
With c.Interior
Select Case xCheck

'Each case represents the value to look for
'and the ColorIndex corresponds to the color
'you want the background to be
Case 100
..ColorIndex = 6
Case 150
..ColorIndex = 9
'Repeat as necessary....

Case Else
'If not a previous condition, no fill
..ColorIndex = 0
End Select
End With
Next
End Sub
'================

'To create a key showing which number corresponds to which color
'you can run this quick macro, which uses row number to represent color
'Run this macro on a blank sheet

Sub CreateKey()
For i = 1 to 56
Cells(i,1).Interior.ColorIndex = i
Next
End Sub

--
Best Regards,

Luke M
"joemc911" wrote in message
...
This could be very simple, but I will lay the groundwork first. I have a
schedule spreadsheet that I import data to from a web based program.
There
are existing filters to remove all formatting of the data and remove that
which we do not use. What remains is a non formatted sheet that other
workbooks link to so we can produce daily sheets. Now the data that is
linked on the other sheets may be for example the number 150. It appears
throughout the sheet and I would like to color any cell that contains the
number 150. The problem is I can't search for 150 because it really
doesn't
exist. All that exists is the linked cell refernece to where this page
gets
the data from.

The action I want to learn to produce is that, based on the resulting
data,
color the cell(s) the chosen color. Something to the effect of if any
cell
in workbook1 contains number 150, resulting from a link to a cell in
workbooka1, to be colored blue. This makes every occurance of 150 in a
cell
change the cell background blue. I would like to have this formulated for
about 25 conditions.

I am willing to do all the work, all the training, all the coding,
whatever
I need to so that I can make this work as we use this weekly and have to
manually alter background colors. I don't want to buy something to do it
since once I establish this formatting code it will not change very often
if
at all.

Thank you in advance for any guidance and help you might provide!


.