painting cells that match criteria

[oleg mirzaev]

Hi all,

i have an input like this:
task due date 1 due date 2 due date 3 due date 4
A 3 5 7 13
B 4 8 13 15
C 2 4 12 13

for each row i need to paint cells in different colors according to the numbers bellow.
example, for first row:
cells 1-3:blue,4-5:red,6-7:yellow,8-13:green.

what is the best way to implement it?
see attachment for clarification.

Thanks in advance.

[ThirtyTwo]

hi oleg

i'm surprised nobody gave you any suggestions to this problem

there are many approaches you could take, the most common would be a vba script to scan through the values and assign formats as appropriate

I've supplied you with an alternative approach

In the attached file I made a copy of your example table, then applied some logic to it so it can identify which dates fall within each of your due dates

To distinguish each due dates I just used the column number as a quick way of identifying each into a group, you could choose another output

I then, for illustrative purposes made a copy of that table which had the formula applied to show how you can use conditional formatting to give the desired output as you'd illustrated in your example image file and excel file, just set the fill color and font color to be the same, different colors for each value used (3,4,5,6)

Using this method does away with any vba coding

(btw - it showed that you made a mistake in your example file for task C, you excluded due date 4)

maybe this method is something you can work with, if you need help in applying it to your actual data let me know and i'll do my best to assist

32

[oleg mirzaev]

wow thanks!
it's really great what you've done, and without VB.
i'll adjust it to my needs, but this is a great reference!
thanks again!

[Sa DQ]

Option Explicit
Sub gpePaitingCells()
 Dim J As Byte, W As Long, MyColor As Byte, MyNum As Long
 For J = 4 To 6
    For W = 6 To 3 Step -1
        MyNum = Cells(J, W).Value
        MyColor = 34 + MyNum
        Cells(J, 7).Resize(, MyNum).Interior.ColorIndex = MyColor
    Next W
 Next J
End Sub