Conditional formating VBA to aply to the entire sheet?

  • Thread starter Thread starter F.G.
  • Start date Start date
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
 
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
 
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.
 
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,
 
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
 
Back
Top