PDA

View Full Version : Coloring range of cells under colored cels



johnnyexit
09-25-2017, 05:09 AM
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
09-25-2017, 05:30 AM
Use Conditional Formatting with Formula Rule =OR(A$3=1,A$3=7)

johnnyexit
09-25-2017, 05:45 AM
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
09-25-2017, 05:52 AM
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
09-25-2017, 06:09 AM
mdmackillop (http://www.vbaexpress.com/forum/member.php?87-mdmackillop) please help, I dont understand.

In attachment.

mdmackillop
09-25-2017, 06:14 AM
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
09-26-2017, 11:41 AM
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
09-26-2017, 02:04 PM
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
09-27-2017, 12:27 PM
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
09-27-2017, 12:44 PM
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
09-28-2017, 11:48 AM
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
09-28-2017, 12:55 PM
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
10-02-2017, 11:10 AM
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
10-02-2017, 11:26 AM
Record a macro and tag it on at the end

johnnyexit
10-03-2017, 05:45 AM
I dont understand what does it means, can you be more specific please.

mdmackillop
10-03-2017, 06:56 AM
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.