Changing individual cells based on entries in adjacent cells
Posted by Jerry on February 05, 2001 6:23 PM
I'm trying to understand how to automatically trigger an Event or Macro for cells in a spreadsheet'
Excel 97
I have 5 Rows in a sample spreadsheet
I would like the background of the cell in the adjacent column to automatically change to the color based on the users entry.
Example:
If the user enters R, G, or B in cell A1 how would I cause the background in cell B1 to change to Red, Green or Blue?
The user then goes to cell A2 and enters R, G, or B in that cell. The background of the adjacent cell B2 should change to the selected color. The color entered in A2 will likely be different than in A1.
Same for A3, A4, and A5
I am aware of WatchIT and Worksheet_Change but those seem to allow change based on a WatchArea, not individual cells.
Please respond via email
Thanks
bjkiral@qwest.net
Jerry Kiral
Bloomington MN
Posted by Dave Hawley on February 06, 2001 12:30 AM
Hi Jerry
Here is an example using the Worksheet_Change event. Note the "Option Compare Text" this will prevent case sensitivity. Remove this for Excel to default to Compare Binary (Case sensitive)
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 1 And Target.Row <= 5 Then
Select Case Target
Case "R"
Target.Offset(0, 1).Interior.ColorIndex = 3
Case "G"
Target.Offset(0, 1).Interior.ColorIndex = 10
Case "B"
Target.Offset(0, 1).Interior.ColorIndex = 5
End Select
End If
End Sub
Hope this helps
Dave
OzGrid Business Applications
Posted by Mark W. on February 06, 2001 2:27 PM
This can easily be accomplished using Conditional Formatting
Jerry, this can be accomplished very easily using Conditional Formatting. After selecting cell B1 just choose the Format Conditional Formatting... menu command, and assign 3 "Formula Is" conditionals. Setup a condition for =$A1="R", =$A1="G", and ="$A1="B". Assign a cell pattern format using the color corresponding to each of the values in cell A1. Finally, copy cell B1, select column B:B and perform a Paste Special... Formats.
Posted by Dave Hawley on February 06, 2001 3:53 PM
Re: This can easily be accomplished using Conditional Formatting
Mark I'm thinking Jerry already knows this as he did ask specifically for an Event or macro and not a formula.
You really should give VBA a go one day, you may even like it :-)
Dave
OzGrid Business Applications
Posted by Dave Hawley on February 06, 2001 4:02 PM
Re: This can easily be accomplished using Conditional Formatting
...Besides why not select Column B and do it in one go ?
Tell me also Mark, what was that time diff using a VLOOKUP nested with a ISNA in a SINGLE cell, using a whole Column to look in compared to not having the ISNA ??? A bit quicker than an entire column on 3 tiered Conditional Formatting. :-)
Sorry Jerry, Don't mind us, personal joke!
OzGrid Business Applications
Posted by Mark W. on February 06, 2001 5:12 PM
Re: This can easily be accomplished using Conditional Formatting
> ...Besides why not select Column B and do it in one go ?
Good idea!
> ...what was that time diff using a VLOOKUP nested with a ISNA in a SINGLE cell...
Since VLOOKUP() employs a sequential search on average it must examine n/2 cells where n is the number of cells in the leftmost column of the lookup range. In the worse case senario where the value being looked up is the nth cell =IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...)) must examine 2n cells while a simple VLOOKUP() must examine only half the number of cells.
Posted by Mark W. on February 06, 2001 5:18 PM
Re: This can easily be accomplished using Conditional Formatting
> I'm thinking Jerry already knows this as he did ask specifically for an Event or macro and not a formula.
Not necessarily...
> You really should give VBA a go one day...
I do use VBA -- but only when necessary.
Posted by Dave Hawley on February 06, 2001 11:58 PM
Re: This can easily be accomplished using Conditional Formatting
Mark, it was a joke :-) I was refering to your comment a few days ago that on your PC you had timed the diff betweem 2 single VLOOKUPS one with a TRUE argument and one without.
Dave
OzGrid Business Applications
Posted by Dave Hawley on February 07, 2001 12:01 AM
Re: This can easily be accomplished using Conditional Formatting
>Not necessarily...
OH NO!!! Mark knows what I'm thinking :-)
Dave
OzGrid Business Applications
