Set cell highlighting and/or fill color programatically?

[brian pope]

Anyone know how I can go about this in Excel? Can it be done in VBA macro or something?

I have two sheets,

First sheet has a grid of boolean values.(T/F)which indicates whether a cell should be highlighted or filled in sheet2 according to an index equal to the rownumber.

abcdefgh
1TTTFFFT
2FFTTFFF
3FFFFFFF
4TTTTTTT
5TFTFTFT
etc for other rows

Second sheet has a "pick list column" to enter a value to index 1st sheet at col c.
What I need to do is to is highlight or fill the corresponding following cells in same row (marked by asterisk) appropriately according to the first sheet. So for example if I enter row indexes 1,3,4,1,2 in col c as below, the cells will highlight according to the index in sheet 1.
I have used H= highlight/fill where value is T above

abcdefghijklmnopq
1 *******
2 1 HHH H <- highlighted/filled according to row 1 in sheet 1
3 3 <- highlighted/filled according to row 3 in sheet 1
3 4 HHHHHHH <- highlighted/filled according to row 4 in sheet 1
4 1 HHH H <- highlighted/filled according to row 1 in sheet 1
5 2 HH <- highlighted/filled according to row 2 in sheet 1
^
|
---picklist column

So basically, as soon as I enter an "index value number" in column c, the cells
in same row in columns e->k should be highlighted according to the indexed row
in sheet 1.

TIA,
Brian.

(e-mail address removed)

 

[Bernie Deitrick]

Brian,

You can use the second sheet's change event. Copy the code below, right click on the sheet tab, select "view code" and paste the code into the window that appears.

Assumptions: You want to enter numbers in column C, your highlighting starts in column D and extends to column J, your first sheet is named "Sheet1", and the table has numbers in column A to denote the index, and the values are "T" (not True - which it would be if they were actual booleans) to indicate which should be highlighted. Highlighting is in red, colorindex 3. The code can be easily modified if any of these assumptions are incorrect.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myIndex As Long
Dim myCell As Range

On Error GoTo NoMatch
If Target.Column = 3 Then
If Application.IsNumber(Target.Value) Then
myIndex = Application.Match(Target.Value, _
Worksheets("Sheet1").Range("A:A"), False)
For Each myCell In Worksheets("Sheet1"). _
Range("B" & myIndex & ":H" & myIndex)
With Cells(Target.Row, myCell.Column + 2)
If myCell.Value = "T" Then
.Interior.ColorIndex = 3
Else
.Interior.ColorIndex = xlNone
End If
End With
Next myCell
End If
End If
Exit Sub
NoMatch:
MsgBox "That number isn't in your table."
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End Sub



Anyone know how I can go about this in Excel? Can it be done in VBA macro or something?

I have two sheets,

First sheet has a grid of boolean values.(T/F)which indicates whether a cell should be highlighted or filled in sheet2 according to an index equal to the rownumber.

abcdefgh
1TTTFFFT
2FFTTFFF
3FFFFFFF
4TTTTTTT
5TFTFTFT
etc for other rows

Second sheet has a "pick list column" to enter a value to index 1st sheet at col c.
What I need to do is to is highlight or fill the corresponding following cells in same row (marked by asterisk) appropriately according to the first sheet. So for example if I enter row indexes 1,3,4,1,2 in col c as below, the cells will highlight according to the index in sheet 1.
I have used H= highlight/fill where value is T above

abcdefghijklmnopq
1 *******
2 1 HHH H <- highlighted/filled according to row 1 in sheet 1
3 3 <- highlighted/filled according to row 3 in sheet 1
3 4 HHHHHHH <- highlighted/filled according to row 4 in sheet 1
4 1 HHH H <- highlighted/filled according to row 1 in sheet 1
5 2 HH <- highlighted/filled according to row 2 in sheet 1
^
|
---picklist column

So basically, as soon as I enter an "index value number" in column c, the cells
in same row in columns e->k should be highlighted according to the indexed row
in sheet 1.

TIA,
Brian.

(e-mail address removed)