Conditional formating VBA to aply to the entire sheet?
 

My apologies I'm newbie on VBA, so don't be frustrated with my
question

I have a sheet with more than 3 CF. I got the Vba code working
corretct. But this works only if I enter the data from the begining,
how do i make it to aplly if the data is already on the sheet.

The code is above:
START OF THE CODE
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim CellVal As Integer
If Target.Cells.Count 1 Then Exit Sub
If Target = "" Or Not IsNumeric(Target) Then Exit Sub
CellVal = Target
Set WatchRange = Range("a:k")


If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case 0 To 3
Target.Interior.ColorIndex = 35
Case 4
Target.Interior.ColorIndex = 33
Case 5
Target.Interior.ColorIndex = 2
Case 6 To 7
Target.Interior.ColorIndex = 44
Case 8 To 10
Target.Interior.ColorIndex = 3
End Select
End If
End Sub
END OF THE CODE

Thank You
Regards,
Frank G

Conditional formating VBA to aply to the entire sheet?
 

Run this simple macro just once to prime them.

Sub RunFromStart()
Dim cell As Range
Dim CellVal As Integer
For Each cell In Range("a:k")

If IsNumeric(cell.Value) Then

Select Case cell.Value
Case 0 To 3
Target.Interior.ColorIndex = 35
Case 4
Target.Interior.ColorIndex = 33
Case 5
Target.Interior.ColorIndex = 2
Case 6 To 7
Target.Interior.ColorIndex = 44
Case 8 To 10
Target.Interior.ColorIndex = 3
End Select
End If
Next cell
End Sub

--
__________________________________
HTH

Bob

"F.G." wrote in message
...
My apologies I'm newbie on VBA, so don't be frustrated with my
question

I have a sheet with more than 3 CF. I got the Vba code working
corretct. But this works only if I enter the data from the begining,
how do i make it to aplly if the data is already on the sheet.

The code is above:
START OF THE CODE
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim CellVal As Integer
If Target.Cells.Count 1 Then Exit Sub
If Target = "" Or Not IsNumeric(Target) Then Exit Sub
CellVal = Target
Set WatchRange = Range("a:k")


If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case 0 To 3
Target.Interior.ColorIndex = 35
Case 4
Target.Interior.ColorIndex = 33
Case 5
Target.Interior.ColorIndex = 2
Case 6 To 7
Target.Interior.ColorIndex = 44
Case 8 To 10
Target.Interior.ColorIndex = 3
End Select
End If
End Sub
END OF THE CODE

Thank You
Regards,
Frank G

Conditional formating VBA to aply to the entire sheet?
 

Option Explicit
Sub testme()
Dim WatchRange As Range
dim Target as range 'just to save typing!

with worksheets("somesheetnamehere")
set watchrange = nothing
on error resume next
set watchrange = intersect(.usedrange, .Range("a:k"))
on error goto 0
end with

if watchrange is nothing then
msgbox "Nothing in that range"
exit sub
end if

for each Target in watchrange.cells
select case target.value
Case 0 To 3
Target.Interior.ColorIndex = 35
Case 4
Target.Interior.ColorIndex = 33
Case 5
Target.Interior.ColorIndex = 2
Case 6 To 7
Target.Interior.ColorIndex = 44
Case 8 To 10
Target.Interior.ColorIndex = 3
End Select
next target

End Sub

(Untested, uncompiled. Watch out for typos!)

This goes in a General module--not behind the worksheet.



"F.G." wrote:

My apologies I'm newbie on VBA, so don't be frustrated with my
question

I have a sheet with more than 3 CF. I got the Vba code working
corretct. But this works only if I enter the data from the begining,
how do i make it to aplly if the data is already on the sheet.

The code is above:
START OF THE CODE
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim CellVal As Integer
If Target.Cells.Count 1 Then Exit Sub
If Target = "" Or Not IsNumeric(Target) Then Exit Sub
CellVal = Target
Set WatchRange = Range("a:k")

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case 0 To 3
Target.Interior.ColorIndex = 35
Case 4
Target.Interior.ColorIndex = 33
Case 5
Target.Interior.ColorIndex = 2
Case 6 To 7
Target.Interior.ColorIndex = 44
Case 8 To 10
Target.Interior.ColorIndex = 3
End Select
End If
End Sub
END OF THE CODE

Thank You
Regards,
Frank G

--

Dave Peterson

Conditional formating VBA to aply to the entire sheet?
 

Thx Guys for your time,

But I'm not being able to mak it work.

I apreciate your help, and will try to mak it work because I guess the
code is right but me I cant put right.

Rgerards,

Conditional formating VBA to aply to the entire sheet?
 

Thanks Dave, Thanks Bob
It is working DAAAAAA
I gues sometimes is good to go for a walk and come back and you see
right there what you were doing wrong for several hours.

Thx guys thx so much

FG