Conditional Formatting or Macro?

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

I have a sheet that I enter simple data and change the
font color, and background color on cell as well as the
one above it and below it. Right now I am doing this all
manually, but its quite a few entries. here is what I am
working with.

If I enter the letter H in cell B4, I change the font to
dark blue, and the background of cells B2:B4 light blue.
If the letter is V, I change the font to red and the
background to pink, and so on.

I have thought about CF but to group these by three would
take forever. Is there another way??

TIA
 
-----Original Message-----
I'm confused about this: C2:C4, but you say you want it to work on A1:AF935.

I assumed you wanted groups of 3 cells starting in row 2 (not 1) and ending in
row 937 (936 rows is a multiple of 3).

And I'm not sure how you make changes. If your group of 3 cells is B2:B4, then
you want to be able to change B2, B3 or B4 or just the bottom of that group.

I guessed that you wanted to change any of the three. In fact, I used a
different font color on the cell that changed--but you can drop this line
("Target.Font.ColorIndex = #") from the code.

If all that's true, this seems to work ok:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AffectedRange As Range

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A2:AF937")) Is Nothing Then Exit Sub

Set AffectedRange = _
Me.Cells(3 * Int((Target.Row + 1) / 3) - 1, Target.Column).Resize(3, 1)

Select Case Target.Value
Case Is = "H"
AffectedRange.Font.ColorIndex = 5
Target.Font.ColorIndex = 3
AffectedRange.Interior.ColorIndex = 8
Case Is = "V"
AffectedRange.Font.ColorIndex = 3
Target.Font.ColorIndex = 4
AffectedRange.Interior.ColorIndex = 45
Case Else
AffectedRange.Font.ColorIndex = xlAutomatic
AffectedRange.Interior.ColorIndex = xlNone
End Select

End Sub

-----Original Message-----
I have a sheet that I enter simple data and change the
font color, and background color on cell as well as the
one above it and below it. Right now I am doing this all
manually, but its quite a few entries. here is what I am
working with.

If I enter the letter H in cell B4, I change the font to
dark blue, and the background of cells B2:B4 light blue.
If the letter is V, I change the font to red and the
background to pink, and so on.

I have thought about CF but to group these by three would
take forever. Is there another way??

TIA

You could use a VBA event-triggered macro.

Right click on the sheet tab and select View Code.

Into the window that opens, paste this code:

===================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AffectedRange As Range
Set AffectedRange = [B2:B4]

If Not Intersect(Target, [B4]) Is Nothing Then
Select Case [B4].Value
Case Is = "H"
AffectedRange.Font.ColorIndex = 5
AffectedRange.Interior.ColorIndex = 8
Case Is = "V"
AffectedRange.Font.ColorIndex = 3
AffectedRange.Interior.ColorIndex = 45
Case Else
AffectedRange.Font.ColorIndex = xlAutomatic
AffectedRange.Interior.ColorIndex = xlNone
End Select
End If
End Sub
===================

Experiment with the colorindex values until you get
Click to expand...
what
you want. An
alternative, but not as flexible, would be to use the color property.




--ron
.
Ron,
Click to expand...

this works great for that specific range. How can I make
it work for my whole sheet? In other words, to work with
C2:C4, D2:D4,E2:E4,C5:C7, and so on. I need this to work
in these groups of three, but the range is A1:AF935.
Thanks
Click to expand...

--

Dave Peterson
(e-mail address removed)
.
sorry I did not explain better. I guess what I am
Click to expand...
looking for is that when I enter the letter "V" or "H"
anywhere in this spreadsheet, I would like the letter to
change to a different color and also have that cell, as
well as the cell above it and below it to change to a
certain background color. The first macro that I got from
Ron was great, but the range was limited to B2:B4

I thought about using CF but to group every three cells
would just take way too long.....

Thanks (and once again, sorry)
Click to expand...
 
sorry I did not explain better. I guess what I am
looking for is that when I enter the letter "V" or "H"
anywhere in this spreadsheet, I would like the letter to
change to a different color and also have that cell, as
well as the cell above it and below it to change to a
certain background color. The first macro that I got from
Ron was great, but the range was limited to B2:B4

I thought about using CF but to group every three cells
would just take way too long.....

Thanks (and once again, sorry)
Click to expand...

Well, that's much easier and can be done using Conditional Formatting.

Select A2
Format/Conditional Formatting.

Condition 1
Formula Is: =OR(A1="V",A3="V",A2="V")
format to taste

Condition 2
Formula Is: =OR(A1="H",A3="H",A2="H")
format to taste

Then using the format painter, select A2 and copy the format to the
AffectedRange.

Two caveats:

1. Condition 1 has priority. So if A2 = "v" and A3 = "h" then A1:A3 will be
formatted according to the condition 1 rule, and A4 will be formatted according
to the condition 2 rule.

2. The formatting of row 1 may change depending on the contents of row 65536


--ron
 
If that macro was correct, then you still wanted the rows grouped in triads--not
just the current cell and one above and one below.

If you did the two adjacent cells--no concern over which row, take one more look
at Ron's Conditional formatting.
 
Back
Top