How Do I...

  • Thread starter Thread starter Michael Koerner
  • Start date Start date
M

Michael Koerner

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
 
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.
 
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,

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.
 
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.Rows.Count
For vCol = 1 To
Worksheets("Sheet2").Range("A1").CurrentRegion.Columns.Count

vID = Worksheets("Sheet2").Range("A1").CurrentRegion.Cells(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.Cells(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
 
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


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.Rows.Count
For vCol = 1 To
Worksheets("Sheet2").Range("A1").CurrentRegion.Columns.Count

vID = Worksheets("Sheet2").Range("A1").CurrentRegion.Cells(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.Cells(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 said:
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
Click to expand...





http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000
Newsgroups
 
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


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.
 
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.
 
Steve;

I really appreciate your help. I have got it to work if I have everything on one
sheet. I had to change the formula a little. I will get around to trying your
method. I'll try and make what I am doing a little clearer

Sheet 1 Column 1 is numbered from 001 to 500 because I have a header row. #001
is in A2, #002 is in A3 and so on. In the adjacent column D* I can have the
value Good, Fair, Poor.

Off to the right I have a set of rows and columns pre numbered

H2 I2 J2..... AA2
001,002,003,....020
021,022.023,.....040 up to 500

My formula in cell H2 with everything on the main sheet looks like this

First Condition
=VLOOKUP($A2,Data,4,FALSE)="Poor" The colour is red

Second Condition
=VLOOKUP($A2,Data,4,FALSE)="Good" The colour is green

Third Condition
=VLOOKUP($A2,Data,4,FALSE)="Fair" The colour yellow

Now, I can leave it all on one sheet. But would like to see if I can get it to
work from 2 sheets, because I am stubborn, also easier for printing purposes
<g>.



--

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.
 
Michael,

Glad to hear that you got it to work.
Conditional Formatting is tricky the first time through. Especially if you
are using formulas as the condition.

Keep us posted on your progress!
 
Still cannot figure out how to get it to work from another sheet. Get the
following error message "You may not use references to other worksheets or
workbooks for conditional formatting criteria."

--

Regards
Michael Koerner


Michael,

Glad to hear that you got it to work.
Conditional Formatting is tricky the first time through. Especially if you
are using formulas as the condition.

Keep us posted on your progress!
 
The formulas you show for you conditional formatting for H2 are incorrect.
If you want H2 to be colored based on the value in H2, then your formula
should include some reference to H2 wouldn't you think

So H2 conditional formatting should look like this

First Condition
=VLOOKUP(H2,Data,4,FALSE)="Poor" The colour is red

Second Condition
=VLOOKUP(H2,Data,4,FALSE)="Good" The colour is green

Third Condition
=VLOOKUP(H2,Data,4,FALSE)="Fair" The colour yellow

I also removed the $ since you don't want to always reference column H if
you copy the formula.

This assumes the if I went to Insert =>Name=>Define and looked at the
definition for data it would appear as

=Sheet1!$A$2:$D$501

$ signs should be included here.

Now you need to go to sheet 2.

Assume you have the same grid on sheet2, but your grid starts in A1 and goes
to T25.

Select A1 to T25, with A1 as the activecell (reverse highlight)

Select format, conditional formatting and put in the formulas

First Condition
=VLOOKUP(A1,Data,4,FALSE)="Poor" The colour is red

Second Condition
=VLOOKUP(A1,Data,4,FALSE)="Good" The colour is green

Third Condition
=VLOOKUP(A1,Data,4,FALSE)="Fair" The colour yellow

Again, with no $ signs so the formula will adjust propertly for the other
cells in the selection.

this works fine for me. I can send you a sample workbook if you would like,
but I think your main problem is using the $A (if you want the sample,
email me with a valid email address - mine is valid).
 
Tom, no need to send anything. Your explanation was right on, and everything is
right once again with the world. Worked like a charm, thank you very much.

--

Regards
Michael Koerner


The formulas you show for you conditional formatting for H2 are incorrect.
If you want H2 to be colored based on the value in H2, then your formula
should include some reference to H2 wouldn't you think

So H2 conditional formatting should look like this

First Condition
=VLOOKUP(H2,Data,4,FALSE)="Poor" The colour is red

Second Condition
=VLOOKUP(H2,Data,4,FALSE)="Good" The colour is green

Third Condition
=VLOOKUP(H2,Data,4,FALSE)="Fair" The colour yellow

I also removed the $ since you don't want to always reference column H if
you copy the formula.

This assumes the if I went to Insert =>Name=>Define and looked at the
definition for data it would appear as

=Sheet1!$A$2:$D$501

$ signs should be included here.

Now you need to go to sheet 2.

Assume you have the same grid on sheet2, but your grid starts in A1 and goes
to T25.

Select A1 to T25, with A1 as the activecell (reverse highlight)

Select format, conditional formatting and put in the formulas

First Condition
=VLOOKUP(A1,Data,4,FALSE)="Poor" The colour is red

Second Condition
=VLOOKUP(A1,Data,4,FALSE)="Good" The colour is green

Third Condition
=VLOOKUP(A1,Data,4,FALSE)="Fair" The colour yellow

Again, with no $ signs so the formula will adjust propertly for the other
cells in the selection.

this works fine for me. I can send you a sample workbook if you would like,
but I think your main problem is using the $A (if you want the sample,
email me with a valid email address - mine is valid).
 
Back
Top