Conditional formating VBA to aply to the entire sheet?

F

F.G.

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
 
B

Bob Phillips

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
 
D

Dave Peterson

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

F.G.

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,
 
F

F.G.

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top