PDA

View Full Version : Solved: refer to value in a cell in a macro



Ger
09-07-2011, 01:14 AM
Hey,

I'm trying to build a macro that colors x-rows depending on a value on sheet ("wk 27 tm 52") cells I312 to GH312.
In the macro i first remove the background color (sub ontkleuren). Then the macro (sub kleuren) must set the background color for x-rows in column a depending on the value on sheet ("wk 27 tm 52" cell I312), x-rows in column B depending on value ("wk 27 tm 52" cell J312) etc.

Sub kleurtjes()
ontkleuren
kleuren
End Sub
Sub ontkleuren()
Dim j As Long, z As Long, x As Long
Sheets("in dienst").Select
Application.ScreenUpdating = False
For j = 1 To 182
z = 4
x = 50
Range(Cells(z, j), Cells(x, j)).Select
With Selection.Interior
.ColorIndex = xlNone
End With
Next j
End Sub
Sub kleuren()
Dim j As Long, z As Long, x As Long
Sheets("in dienst").Select
For j = 1 To 182
z = 4
x = z + the value on sheet (wk 27 tm 52 cell I312 to GH312
Range(Cells(z, j), Cells(x, j)).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Next j
Application.ScreenUpdating = True
End Sub


Ger

Ger
09-07-2011, 05:00 AM
I found it by trying. My problem was that X always had the same value (I312 + 3).


Dim dienst As Integer
Sub kleurtjes()
ontkleuren
kleuren
End Sub
Sub ontkleuren()
Sheets("in dienst").Activate
Application.FindFormat.Interior.ColorIndex = 6
Application.ReplaceFormat.Interior.ColorIndex = xlNone
Cells.Replace What:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=True
End Sub
Sub kleuren()
Application.ScreenUpdating = False
Dim j As Long, z As Long, x As Long, y As Long
Sheets("in dienst").Select
For y = 9 To 190
j = j + 1
z = 4
dienst = Worksheets("wk 27 tm 52").Cells(312, y).Value
x = 3 + dienst
Range(Cells(z, j), Cells(x, j)).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Next y
Application.ScreenUpdating = True
End Sub


Ger