Coloring range of cells under colored cels

[johnnyexit]

Hello everyone.

I have a little problem. This is kind of excel sheet of cleaning table in factory. I made it with date function so when I open every month i get the list
of days (29,30,31 depend of month) and colored weekends.


Sub test()
Dim r As Range, c As Range
Dim B8 As Range
Set r = Range(Range("B7"), Range("AF7"))
For Each c In r
If Weekday(c) = 1 Or Weekday(c) = 7 Then
c.Interior.ColorIndex = 3
End If
Next c
End Sub

Now I need to color in red every cell under Weekend from B8 to AF16, for example if now comes October, my weekends will be different from September and different cells needs to be colored red depending on color weekends.

Can you help me please.

20457

[mdmackillop]

Use Conditional Formatting with Formula Rule =OR(A$3=1,A$3=7)

[johnnyexit]

Yes I now that rule, but with combination of that rule VBA code wont delete me a content od cells

Sub Macro1()

Range("B9:AF16").Select
For Each cell In Selection
If cell.Interior.Color = RGB(255, 0, 0) Then

cell.ClearContents
End If
Next

End Sub

[mdmackillop]

Conditional formatting does not change the Interior.Color of a cell. Refer to the cells used by the CF

For Each cell In Range("B9:AF16")
If Cells(3, cell.Column) = 1 Or Cells(3, cell.Column) = 7 Then
cell.ClearContents
End If
Next


Please use Code Tags of # button to format code

[johnnyexit]

mdmackillop (http://www.vbaexpress.com/forum/member.php?87-mdmackillop) please help, I dont understand.

In attachment.

[mdmackillop]

As you are using Row 7 in your CF, You need to refer to that row in your macro

Sub Macro1()


For Each cell In Range("B9:AF16")
If Cells(7, cell.Column) = 1 Or Cells(7, cell.Column) = 7 Then
cell.ClearContents
End If
Next
End Sub

[johnnyexit]

Ok mdmackillop I figured out your post from yesterday.

Can you help me with next. In Row 9--> from B9 to AF9 i need to put text P3 in cells which are not red but on evrey third cell like you can see in pic.20482

I used for test but it wont work and i dont know how to put in every third column exept red cells.

If Range("B9:AF9").Interior.Color = RGB(255, 0, 0) Then Range("B9:AF9").Value = ""
If Range("B9:AF9").Interior.Color = RGB(255, 255, 255) Then Range("B9:AF9").Value = "P3"

[mdmackillop]

in B9, copy across and down =IF(AND(B$7<>1,B$7<>7,MOD(NETWORKDAYS($B$8,B$8),3)=MOD(ROW(),3)),"p3","")
This will not insert exactly as shown but will insert p3 in every 3rd cell.

[johnnyexit]

How can I found out in VBA colors of cells which are colored with condition formatting in range from B9:AF9??
If it is cell colored red with cond form to put value in cell "NA"

[mdmackillop]

Remove the CF and use this to colour the cells if you will find that easier

Sub Test()
Dim r As Range
Set r = Range("B7:AF7")
For Each cel In r
If Weekday(cel) = 1 Or Weekday(cel) = 7 Then
cel.Resize(10).Interior.ColorIndex = 3
End If
Next cel
End Sub

[johnnyexit]

Thanks mate, VBA is much better then CF.
And for the last step i need help from you guys - masters of programming :hi:. I need code to repeat c.Value="P3 in every third cell" but skip cells which are colored red. (you can see picture in attachment) In range B9:AF9 --> PersonA
20510

And PersonB needs to be +1 in days based on Person A
PersonC needs to be +1 in days based on Person B

Thanks in advance.:dunno

[mdmackillop]

Sub P3()Dim r As Range, x As Long
x = [SumProduct((Len(B5:AF5) > 0)*1)]
Set r = Cells(5, "B").Resize(, x).Offset(2)
For i = 0 To 2
m = 0
For j = 1 To x
If r(j) <> 1 And r(j) <> 7 Then
If m Mod 3 = i Then r(j).Offset(2 + i) = "p3"
m = m + 1
End If
Next j
Next i
End Sub

[johnnyexit]

oooo thx, it is working perfect.
And I need one more solution, "P5" needs to be in white cells but four daysin a row like pic below

20548

[mdmackillop]

Record a macro and tag it on at the end

[johnnyexit]

I dont understand what does it means, can you be more specific please.

[mdmackillop]

Put P5 in each cell if the cell is not red and the next cell is not red. Please remember, we are here to assist, not to do all the work. If you have issues with your code, please post it.