Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet_Change | Excel Programming | |||
Worksheet_Change | Excel Programming | |||
More Then one Worksheet_Change | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming |