Prev: paper size options in page setup
Next: Dates for European Summer Time
From: joemc911 on 23 Mar 2010 14:10
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!
From: Luke M on 23 Mar 2010 14:49
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" <joemc911(a)discussions.microsoft.com> wrote in message
news:EE8EEA39-CF38-4AB2-A3CC-4559BEE6C90D(a)microsoft.com...
> 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!


 | 
Pages: 1
Prev: paper size options in page setup
Next: Dates for European Summer Time