I have a workbook with 2 active sheets. Sheet One contains the following
headings


ID No. | Courtesy of | Photo Quality ...
001 Poor
002 Good

Sheet 2 is a grid of numbers from 001 - 1000.

How would I have the background colour for the numbered cells in sheet 2 change
to red (poor) yellow (fair) green (good) to match the ID No. and Photo quality
from sheet 1?

Any assistance, as always, is greatly appreciated
--

Regards
Michael Koerner

Hi,

You can do that with conditional formatting. Please select your range to
apply the conditions, enter the conditions shown below. and change the
formats for each condition. You need to give a name the range on sheet1. if
you use directly range area like "Sheet1!A1:C6" excel gives error. in the
code below I use "Data".



First Condition
=VLOOKUP($A2,Data,3,FALSE)="Poor"

Second Condition
=VLOOKUP($A3,Data,3,FALSE)="Good"

Third Condition
=VLOOKUP($A4,Data,3,FALSE)="Fair"

--
Regards

Haldun Alay

To e-mail me, please remove AT and DOT from my e-mail address.



"Michael Koerner" , iletide þunu yazdý
...
I have a workbook with 2 active sheets. Sheet One contains the following
headings


ID No. | Courtesy of | Photo Quality ...
001 Poor
002 Good

Sheet 2 is a grid of numbers from 001 - 1000.

How would I have the background colour for the numbered cells in sheet 2
change
to red (poor) yellow (fair) green (good) to match the ID No. and Photo
quality
from sheet 1?

Any assistance, as always, is greatly appreciated
--

Regards
Michael Koerner

As I don't work with Excel on a regular basis, this is all new to me. Where do I
put what? Could I email you the sheet?

--

Regards
Michael Koerner


"Haldun Alay" <haldunalayATyahooDOTcom wrote in message
...
Hi,

You can do that with conditional formatting. Please select your range to
apply the conditions, enter the conditions shown below. and change the
formats for each condition. You need to give a name the range on sheet1. if
you use directly range area like "Sheet1!A1:C6" excel gives error. in the
code below I use "Data".



First Condition
=VLOOKUP($A2,Data,3,FALSE)="Poor"

Second Condition
=VLOOKUP($A3,Data,3,FALSE)="Good"

Third Condition
=VLOOKUP($A4,Data,3,FALSE)="Fair"

--
Regards

Haldun Alay

To e-mail me, please remove AT and DOT from my e-mail address.



"Michael Koerner" , iletide þunu yazdý
...
I have a workbook with 2 active sheets. Sheet One contains the following
headings


ID No. | Courtesy of | Photo Quality ...
001 Poor
002 Good

Sheet 2 is a grid of numbers from 001 - 1000.

How would I have the background colour for the numbered cells in sheet 2
change
to red (poor) yellow (fair) green (good) to match the ID No. and Photo
quality
from sheet 1?

Any assistance, as always, is greatly appreciated
--

Regards
Michael Koerner

Michael,

There is a pretty good guide in the Help. Just look up 'conditional
formatting'.

To apply it - go to the Format menu and select Conditional Formatting.

--
sb
"Michael Koerner" wrote in message
...
As I don't work with Excel on a regular basis, this is all new to me.
Where do I
put what? Could I email you the sheet?

--

Regards
Michael Koerner


"Haldun Alay" <haldunalayATyahooDOTcom wrote in message
...
Hi,

You can do that with conditional formatting. Please select your range to
apply the conditions, enter the conditions shown below. and change the
formats for each condition. You need to give a name the range on sheet1.
if
you use directly range area like "Sheet1!A1:C6" excel gives error. in the
code below I use "Data".



First Condition
=VLOOKUP($A2,Data,3,FALSE)="Poor"

Second Condition
=VLOOKUP($A3,Data,3,FALSE)="Good"

Third Condition
=VLOOKUP($A4,Data,3,FALSE)="Fair"

--
Regards

Haldun Alay

To e-mail me, please remove AT and DOT from my e-mail address.



"Michael Koerner" , iletide þunu yazdý
...
I have a workbook with 2 active sheets. Sheet One contains the following
headings


ID No. | Courtesy of | Photo Quality ...
001 Poor
002 Good

Sheet 2 is a grid of numbers from 001 - 1000.

How would I have the background colour for the numbered cells in sheet 2
change
to red (poor) yellow (fair) green (good) to match the ID No. and Photo
quality
from sheet 1?

Any assistance, as always, is greatly appreciated
--

Regards
Michael Koerner

Like I stated in my initial post Excel is not my thing. I looked at the Help
files before I came here. Still having trouble understanding.

--

Regards
Michael Koerner


"steve" wrote in message
...
Michael,

There is a pretty good guide in the Help. Just look up 'conditional
formatting'.

To apply it - go to the Format menu and select Conditional Formatting.

--
sb
"Michael Koerner" wrote in message
...
As I don't work with Excel on a regular basis, this is all new to me.
Where do I
put what? Could I email you the sheet?

--

Regards
Michael Koerner


"Haldun Alay" <haldunalayATyahooDOTcom wrote in message
...
Hi,

You can do that with conditional formatting. Please select your range to
apply the conditions, enter the conditions shown below. and change the
formats for each condition. You need to give a name the range on sheet1.
if
you use directly range area like "Sheet1!A1:C6" excel gives error. in the
code below I use "Data".



First Condition
=VLOOKUP($A2,Data,3,FALSE)="Poor"

Second Condition
=VLOOKUP($A3,Data,3,FALSE)="Good"

Third Condition
=VLOOKUP($A4,Data,3,FALSE)="Fair"

--
Regards

Haldun Alay

To e-mail me, please remove AT and DOT from my e-mail address.



"Michael Koerner" , iletide þunu yazdý
...
I have a workbook with 2 active sheets. Sheet One contains the following
headings


ID No. | Courtesy of | Photo Quality ...
001 Poor
002 Good

Sheet 2 is a grid of numbers from 001 - 1000.

How would I have the background colour for the numbered cells in sheet 2
change
to red (poor) yellow (fair) green (good) to match the ID No. and Photo
quality
from sheet 1?

Any assistance, as always, is greatly appreciated
--

Regards
Michael Koerner

Michael,

OK! Let's walk through this.

These are the formulas Haldun supplied

First Condition
=VLOOKUP($A2,Data,3,FALSE)="Poor"

Second Condition
=VLOOKUP($A3,Data,3,FALSE)="Good"

Third Condition
=VLOOKUP($A4,Data,3,FALSE)="Fair"


Now with this email opened... so you can come back and copy each
formula.


Go to Excel and select sheet 1.
Select your data range. (from you initial post this should be A1:C1000)
Go to the Insert menu and select Name and select Define.
Enter 'Data' (without the apostrophies) in the top box and click OK

Now select sheet 2.
Select the column that you want to have the format.
(click the D if you want column D)
Go to the 'Format menu and select 'Conditional Formatting'
A dialog box will open and you should see
'Condition 1 with a box under it with 'Cell Value Is'
Click on the (tiny) arrow (just to the right) and select
'Formula Is'
Go back to this email and copy the first formula and paste it into the box
(that is immediately to the right of 'Formula Is'
Click the 'Format' button and select and change Font, Border, Patterns
to your liking. For background color just go to Patterns. Select the
color for this condition and click OK.

Now you should be back to the dialog box.
Click the 'Add' button and you should see 'Condition 2'
Repeat the above process for the second condition.

And repeat again for the third condition.

Post back with your progress.
--
sb
"Michael Koerner" wrote in message
...
Like I stated in my initial post Excel is not my thing. I looked at the
Help
files before I came here. Still having trouble understanding.

--

Regards
Michael Koerner


"steve" wrote in message
...
Michael,

There is a pretty good guide in the Help. Just look up 'conditional
formatting'.

To apply it - go to the Format menu and select Conditional Formatting.

--
sb
"Michael Koerner" wrote in message
...
As I don't work with Excel on a regular basis, this is all new to me.
Where do I
put what? Could I email you the sheet?

--

Regards
Michael Koerner


"Haldun Alay" <haldunalayATyahooDOTcom wrote in message
...
Hi,

You can do that with conditional formatting. Please select your range to
apply the conditions, enter the conditions shown below. and change the
formats for each condition. You need to give a name the range on sheet1.
if
you use directly range area like "Sheet1!A1:C6" excel gives error. in
the
code below I use "Data".



First Condition
=VLOOKUP($A2,Data,3,FALSE)="Poor"

Second Condition
=VLOOKUP($A3,Data,3,FALSE)="Good"

Third Condition
=VLOOKUP($A4,Data,3,FALSE)="Fair"

--
Regards

Haldun Alay

To e-mail me, please remove AT and DOT from my e-mail address.



"Michael Koerner" , iletide þunu yazdý
...
I have a workbook with 2 active sheets. Sheet One contains the
following
headings


ID No. | Courtesy of | Photo Quality ...
001 Poor
002 Good

Sheet 2 is a grid of numbers from 001 - 1000.

How would I have the background colour for the numbered cells in sheet
2
change
to red (poor) yellow (fair) green (good) to match the ID No. and Photo
quality
from sheet 1?

Any assistance, as always, is greatly appreciated
--

Regards
Michael Koerner

Hi Michael

Try this, it provides a basic framework to develop and meet your exact
needs......

Good Luck
Nigel

add the following to a module in the workbook...........

Public Sub ColourCode()
Dim vQuality As String
Dim vID As String
Dim vRow As Long, vCol As Long

For vRow = 1 To Worksheets("Sheet2").Range("A1").CurrentRegion.Row s.Count
For vCol = 1 To
Worksheets("Sheet2").Range("A1").CurrentRegion.Col umns.Count

vID = Worksheets("Sheet2").Range("A1").CurrentRegion.Cel ls(vRow,
vCol).Value
vQuality = ""
On Error Resume Next
vQuality = Application.WorksheetFunction.VLookup _
(vID, Worksheets("Sheet1").Range("A1").CurrentRegion,
3, 0)
With Worksheets("Sheet2").Range("A1").CurrentRegion.Cel ls(vRow,
vCol).Interior
Select Case vQuality
Case "Good": .ColorIndex = 4: .Pattern = xlSolid
Case "Fair": .ColorIndex = 44: .Pattern = xlSolid
Case "Poor": .ColorIndex = 3: .Pattern = xlSolid
End Select
End With
Next
Next
End Sub


"Michael Koerner" wrote in message
...
I have a workbook with 2 active sheets. Sheet One contains the following
headings


ID No. | Courtesy of | Photo Quality ...
001 Poor
002 Good

Sheet 2 is a grid of numbers from 001 - 1000.

How would I have the background colour for the numbered cells in sheet 2
change
to red (poor) yellow (fair) green (good) to match the ID No. and Photo
quality
from sheet 1?

Any assistance, as always, is greatly appreciated
--

Regards
Michael Koerner







----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

Thanks Nigel. I think I need a little more than Luck <g My knowledge of VBA, is
less than my knowledge of Excel. I would also like the change to be
instantaneous. when I change/enter a condition in column D on sheet one I would
like the background colour to change according to the corresponding number in
sheet 2.

--

Regards
Michael Koerner


"Nigel" wrote in message
...
Hi Michael

Try this, it provides a basic framework to develop and meet your exact
needs......

Good Luck
Nigel

add the following to a module in the workbook...........

Public Sub ColourCode()
Dim vQuality As String
Dim vID As String
Dim vRow As Long, vCol As Long

For vRow = 1 To Worksheets("Sheet2").Range("A1").CurrentRegion.Row s.Count
For vCol = 1 To
Worksheets("Sheet2").Range("A1").CurrentRegion.Col umns.Count

vID = Worksheets("Sheet2").Range("A1").CurrentRegion.Cel ls(vRow,
vCol).Value
vQuality = ""
On Error Resume Next
vQuality = Application.WorksheetFunction.VLookup _
(vID, Worksheets("Sheet1").Range("A1").CurrentRegion,
3, 0)
With Worksheets("Sheet2").Range("A1").CurrentRegion.Cel ls(vRow,
vCol).Interior
Select Case vQuality
Case "Good": .ColorIndex = 4: .Pattern = xlSolid
Case "Fair": .ColorIndex = 44: .Pattern = xlSolid
Case "Poor": .ColorIndex = 3: .Pattern = xlSolid
End Select
End With
Next
Next
End Sub


"Michael Koerner" wrote in message
...
I have a workbook with 2 active sheets. Sheet One contains the following
headings


ID No. | Courtesy of | Photo Quality ...
001 Poor
002 Good

Sheet 2 is a grid of numbers from 001 - 1000.

How would I have the background colour for the numbered cells in sheet 2
change
to red (poor) yellow (fair) green (good) to match the ID No. and Photo
quality
from sheet 1?

Any assistance, as always, is greatly appreciated
--

Regards
Michael Koerner







----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---