Conditional Format on "Active Cell"

M

Michael

I have a spreadsheet where the rows in Column A are descriptions and Columns
B thru H are Mon thru Fri.

As you arrow down entering dollar amounts by day for each description I
would like the Description is say A44 to bold or change color when the cell
in say H44 is active.

Is this possible?

Thanks,

Mike
 
J

JE McGimpsey

I'd suggest you take a look at Chip Pearson's RowLiner add-in:

http://www.cpearson.com/excel/RowLiner.htm

But if you just want to change the color of the cell in column A:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim rArea As Range
Dim rCell As Range

Columns(1).Font.ColorIndex = xlColorIndexAutomatic
On Error Resume Next
For Each rArea In Intersect(Target.Cells, Range("B:H"))
For Each rCell In rArea
Cells(rCell.Row, 1).Font.ColorIndex = 5
Next rCell
Next rArea
On Error GoTo 0
End Sub
 
F

Frank Kabel

Hi Michael
yes it is possible (using the Selection_change event for this). But
you'll loose your 'Undo' functionality. But you may try the following
code in your worksheet module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Me.Range("B1:H100")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo errhandler
Application.EnableEvents = False
Me.Range("A:A").Interior.ColorIndex = xlNone
Me.Range("A" & Target.row).Interior.ColorIndex = 3

errhandler:
Application.EnableEvents = True
End Sub
 
C

cincode5

Michael,

Assumming you know how to use Conditional Formatting enter "Formula Is" in cell A2 (description cell) as such:

=OR(B2<>"",C2<>"",D2<>"",E2<>"",F2<>"",G2<>"",H2<>"")

then set the format for the desired font/background color
 
M

Michael

I hate to appear dense (I am, I just hate to appear it) but how and where do
I enter enter/insert a "Private Sub Worksheet"?

I also checked out the "RowLiner" suggested in the previous reply by JE
McGimpsey. That's really cool and it's something I can use but I would like
to be able to give the spreadsheet to someone and have the function work
without having to include an addon.

Thanks,
Mike
 
F

Frank Kabel

Hi
to get into the worksheet module try the following:
- open your workbook and goto the specific worksheet
- right-click onf the tab name
- choose 'Code'
- in the appearing editor window paste the code
- close the editor, save the file and try it out
 
C

Corey

Another option you could use for "Formula Is":

=IF(ABS(SUM(B2:H2))>0,TRUE,FALSE)

and set your font/background
-----Original Message-----
Michael,

Assumming you know how to use Conditional Formatting
Click to expand...
enter "Formula Is" in cell A2 (description cell) as such:
 
M

Michael

Thanks Frank!

That did the trick. I've never played around in the Code section. I was
trying to change your solution to change the interior color to multiple
cells (the "description" actually spans multiple columns) but couldn't get
it to work. I played around in the help file and it looks like you should
be able to change font color, style and all kinds of things. Pretty cool.
I really need to learn more about Excel. I think I'm only using about 10%
of the applications power!

Mike
 
Top