Conditional formatting

[puiuluipui]

Hi, i need a formula to add in conditional formatting, that will change the
color of a cell if a certain condition is met.
I have a table with datas. I need to change the patter color of the rows
(rangeA:G) if G is emty.
I need to change the color only if the above and below cells are not empty

Ex:
D
1 john
2 john
3
4 john

i need the empty row (A:G) to be blue
Can this be done?
Thanks!

 

[Simon Lloyd]

This should do what you want


VBA Code:
--------------------


Private Sub Worksheet_Change(ByVal Target As Range
Dim Rng As Range, MyCell As Rang
Set Rng = Me.Range("A2:A" & Me.Range("A" & Rows.Count).End(xlUp).Row
For Each MyCell In Rn
If MyCell <> "" And MyCell.Offset(-1, 0) = "" And MyCell.Offset(1, 0) = "" The
MyCell.Interior.ColorIndex =
End I
Next MyCel
End Sub
--------------------




*How to Save a Worksheet Event Macro*
1. *Copy* the macro above by clicking *,* then *Righ
Click* selected code and *Copy.*
2. Open your Workbook and *Right Click* on the *Worksheet's Name Tab
for the Worksheet the macro will run on.
3. *Left Click* on *View Code* in the pop up menu.
4. *Paste* the macro code using *CTRL+V*
5. Make any custom changes to the macro if needed at this time.
6. *Save* the macro in your Workbook using *CTRL+S*



Hi, i need a formula to add in conditional formatting, that will chang
th
color of a cell if a certain condition is met
I have a table with datas. I need to change the patter color of th
row
(rangeA:G) if G is emty
I need to change the color only if the above and below cells are no
empt

Ex

1 joh
2 joh

4 joh

i need the empty row (A:G) to be blu
Can this be done
Thanks!


--
Simon Lloyd

Regards
Simon Lloyd
'Excel Chat' (http://www.thecodecage.com/forumz/chat.php)

 

[Simon Lloyd]

Slight amendment to the code to reset the cell colour


VBA Code:
--------------------


Private Sub Worksheet_Change(ByVal Target As Range
Dim Rng As Range, MyCell As Rang
Set Rng = Me.Range("A2:A" & Me.Range("A" & Rows.Count).End(xlUp).Row
For Each MyCell In Rn
If MyCell <> "" And MyCell.Offset(-1, 0) = "" And MyCell.Offset(1, 0) = "" The
MyCell.Interior.ColorIndex =
ElseIf MyCell <> "" Or MyCell.Offset(-1, 0) <> "" Or MyCell.Offset(1, 0) <> "" The
MyCell.Interior.ColorIndex = xlNon
End I
Next MyCel
End Sub
--------------------

This should do what you want

VBA Code:
--------------------Private Sub Worksheet_Change(ByVal Target As Range
Dim Rng As Range, MyCell As Rang
Set Rng = Me.Range("A2:A" & Me.Range("A" & Rows.Count).End(xlUp).Row
For Each MyCell In Rn
If MyCell <> "" And MyCell.Offset(-1, 0) = "" And MyCell.Offset(1, 0) = "" The
MyCell.Interior.ColorIndex =
End I
Next MyCel
End Sub
--------------------

HOW TO SAVE A WORKSHEET EVENT MACRO[/B]

1. *COPY* THE MACRO ABOVE BY CLICKING *,* THE
*RIGHT CLICK* SELECTED CODE AND *COPY.*
2. OPEN YOUR WORKBOOK AND *RIGHT CLICK* ON THE *WORKSHEET'S NAME TAB
FOR THE WORKSHEET THE MACRO WILL RUN ON.
3. *LEFT CLICK* ON *VIEW CODE* IN THE POP UP MENU.
4. *PASTE* THE MACRO CODE USING *CTRL+V*
5. MAKE ANY CUSTOM CHANGES TO THE MACRO IF NEEDED AT THIS TIME.
6. *SAVE* THE MACRO IN YOUR WORKBOOK USING CTRL+S


--
Simon Lloyd

Regards
Simon Lloyd
'Excel Chat' (http://www.thecodecage.com/forumz/chat.php)