Advanced "Conditional" formatting which changes by cell selection

C

cosva

Hi,

I'll try to explain my problem. In column A i can have 5 different
numbers: 1,2,3,4 and 5. So the value of any cell in column A (except
for rows 1,2,3,4,5 - they are heading rows ) can be 1 to 5.
Row 1,2,3,4 and 5 are heading rows.
This is what i would like: when I'm in some row (for example row 34 -
and the value of cell A34 is for example 3), and certain column(lets
say column H), I would like cell H3 to become let's say bold(or change
background color or both) - H3 because when the value in cell A34 is 3
that means that heading row 3 is used and needs to be bolded (but now
the whole row just the cell above my selected cell but in the
appropriate heading row - so if A34 is 1 cell H1 will be bolded and so
on).
I have some knowledge of VB so if I need to use VB here I think i
could manage.
If you have any sugestions it would mean a lot to me!
If you don't understand :) my problem plese say and I'll try to
explain again :)

Cheers,

Marko vaco
 
A

Andrew Taylor

And conditional formatting works with 3 conditions? I need five
conditions.-

If I understand you correctly you don't need 5 conditions; you need
one condition in each of cells H1 to H2, i.e.

H1: bold if column A of currently selected row = 1, normal otherwise
H2: bold if column A of currently selected row = 2, normal otherwise
etc.


To do that you could use conditional formatting using the formula
option. There are two problems about this (both qualified by "as
far as I know"):

1. There is no worksheet function to tell you anything about what the
current selection is, or what it contains. To get round this you can
write a UDF: (beware word-wrap)

Public Function ValueInSelectionColumnA()

Application.Volatile ' force recalc even if VBA thinks it's not
needed

On Error Resume Next ' avoid error if Selection is not a range of
cells

ValueInSelectionColumnA = ActiveSheet.Cells(Selection.Cells(1,
1).Row, 1).Value

On Error GoTo 0

End Function

Then in H1 to H5 set up Conditional Formatting with a formula of
=ValueInSelectionColumnA()=ROW() , format Bold

This sort of works, but the formatting only works when you
recalculate, not if you just move
around the sheet (Problem 2). To get round that you need to use the
SelectionChange
event for the worksheet:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iRow As Integer
For iRow = 1 To 5
ActiveSheet.Cells(iRow, "H").Formula = ActiveSheet.Cells(iRow,
"H").Formula ' force recalc
Next
End Sub


Doing all this seems to achieve what I think you want - I hope it
makes some sense

Andrew
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top