Formatting and Worksheet_Change
 

Hi There,
I am new here so let's see...

I am having hard time trying to make VBA code.
I am trying to make a code that will check range B4:AZ4 and if there is a
value 7, it will make cells from row 5 to row e.g. 500 "7" coloured as grey.
I am able to get this far, but it should work out when there are changes in
rows 5 to 500. I want it work when I drag and drop/enter values/delete values
to run this.

Please, Help me, I am going nuts over this.... Thanks in advance.

Formatting and Worksheet_Change
 

Sorry, I will add my code he
Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range, rng2 As Range
Dim rCell As Range
Dim i As Long
Dim arr As Variant

Const Month As String = "7"

Set rng = Me.Range("B4:AZ4")

If Not Intersect(Target, rng) Is Nothing Then

For Each rCell In rng.Cells
If UCase(rCell.Value) = Month Then
rCell(2).Resize(500).Interior.ColorIndex = 15

Else
rCell(2).Resize(500).Interior.ColorIndex = xlNone

End If

Next
End If

End Sub

I also want to make another format for number 12 :o)

Thanks...

"MakeLei" wrote:

Hi There,
I am new here so let's see...

I am having hard time trying to make VBA code.
I am trying to make a code that will check range B4:AZ4 and if there is a
value 7, it will make cells from row 5 to row e.g. 500 "7" coloured as grey.
I am able to get this far, but it should work out when there are changes in
rows 5 to 500. I want it work when I drag and drop/enter values/delete values
to run this.

Please, Help me, I am going nuts over this.... Thanks in advance.

Formatting and Worksheet_Change
 

So what isn't working with your current solution?

--
Regards,
Tom Ogilvy


"MakeLei" wrote:

Sorry, I will add my code he
Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range, rng2 As Range
Dim rCell As Range
Dim i As Long
Dim arr As Variant

Const Month As String = "7"

Set rng = Me.Range("B4:AZ4")

If Not Intersect(Target, rng) Is Nothing Then

For Each rCell In rng.Cells
If UCase(rCell.Value) = Month Then
rCell(2).Resize(500).Interior.ColorIndex = 15

Else
rCell(2).Resize(500).Interior.ColorIndex = xlNone

End If

Next
End If

End Sub

I also want to make another format for number 12 :o)

Thanks...

"MakeLei" wrote:

Hi There,
I am new here so let's see...

I am having hard time trying to make VBA code.
I am trying to make a code that will check range B4:AZ4 and if there is a
value 7, it will make cells from row 5 to row e.g. 500 "7" coloured as grey.
I am able to get this far, but it should work out when there are changes in
rows 5 to 500. I want it work when I drag and drop/enter values/delete values
to run this.

Please, Help me, I am going nuts over this.... Thanks in advance.

Formatting and Worksheet_Change
 

Hi and thanks for fast reply,
How would I be able to activate the code when there are changes in rows 5
to 500... Otherwise it works well.
BR
MakeLei

"Tom Ogilvy" wrote:

So what isn't working with your current solution?

--
Regards,
Tom Ogilvy


"MakeLei" wrote:

Sorry, I will add my code he
Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range, rng2 As Range
Dim rCell As Range
Dim i As Long
Dim arr As Variant

Const Month As String = "7"

Set rng = Me.Range("B4:AZ4")

If Not Intersect(Target, rng) Is Nothing Then

For Each rCell In rng.Cells
If UCase(rCell.Value) = Month Then
rCell(2).Resize(500).Interior.ColorIndex = 15

Else
rCell(2).Resize(500).Interior.ColorIndex = xlNone

End If

Next
End If

End Sub

I also want to make another format for number 12 :o)

Thanks...

"MakeLei" wrote:

Hi There,
I am new here so let's see...

I am having hard time trying to make VBA code.
I am trying to make a code that will check range B4:AZ4 and if there is a
value 7, it will make cells from row 5 to row e.g. 500 "7" coloured as grey.
I am able to get this far, but it should work out when there are changes in
rows 5 to 500. I want it work when I drag and drop/enter values/delete values
to run this.

Please, Help me, I am going nuts over this.... Thanks in advance.