Advanced "Conditional" formatting which changes by cell selection

[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

 

[Guest]

have you tried conditional formatting using a formula rather than value?

 

[cosva]

have you tried conditional formatting using a formula rather than value?

I don't know how to start!!
Any idea?

Marko Svaco

 

[cosva]

I don't know how to start!!
Any idea?

Marko Svaco

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

 

[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