Do I have to use Conditional Formatting or a Macro?

[tanilov]

Hi All,
I have a table like this:

Value1 Value2 Value3
aq x 3
aa y 5

The cells under 'Value2' already have a ConditionalFormatting (with 3
conditions) to change the background color.

I'd like to know if it's possible to apply a format (ie just border) to
the entire row, and copy the ConditionalFormatting regarding the
'Value2' column, as soon as I insert a new record in 'Value1'. That is,
when cells under 'Value' are not blank, then the row will have a
border, and cell under 'Value2' has ConditionalFormatting to change
background.

I don't know if this is possible just using ConditionalFormatting(How?)
or I have to use a Macro (how???).

Thanks a lot for your help,
Tanilo

 

[Otto Moehrbach]

Tanilo
The following sheet macro will do what you want if I read you correctly.
As written this macro will execute if an entry is made in Column A from A2
to A100. It will put a border around the 3 cells and it will copy the
Conditional Formatting from the cell above to the target row in Column B.
Post back if this is not correct or you need more.
This macro is a sheet macro and must be placed in the sheet module of your
sheet. To access the sheet module, right-click on the sheet tab, select
View Code. Paste this macro into that module. Click on the "X" at the top
right to return to your sheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("A2", "A100")) Is Nothing Then
Target.Offset(-1, 1).Copy Target.Offset(, 1)
Target.Offset(, 1).ClearContents
Target.Resize(, 3).Borders(xlEdgeLeft).LineStyle = xlContinuous
Target.Resize(, 3).Borders(xlEdgeTop).LineStyle = xlContinuous
Target.Resize(, 3).Borders(xlEdgeBottom).LineStyle = xlContinuous
Target.Resize(, 3).Borders(xlEdgeRight).LineStyle = xlContinuous
End If
End Sub

 

[tanilov]

Otto Moehrbach ha scritto:

[CUT]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("A2", "A100")) Is Nothing Then

Hi Otto,
thanks a lot for your help. This code works great :-D

So... I had to use macro

Thanks again,
Ciao,
Tanilo