VBA loop

[tmagan]

Hello Folks,
This is TAMIL, new to the forum and Excel VBA.
Speaking of which, I am working on a very small VBA program and ended up nowhere. So, I am looking for some experts help here.
I have a numbers in three cells under a title RED, Yellow and Green. Here I want cells next to this to change its color to RED, Yellow and green based on number on the reference cell.
i.e., If number under the title RED says 10, it should make 10 cells RED followed by the other colors.

How do I do this?

 

[tmagan]

Here is the update given your columns

Book2
	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC
1	Red	Yellow	Gree
2	2	3	10
3			40
4
5
Sheet1

Cell Formulas
Range	Formula
AN3	AN3	=COLUMN()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AO2:BG2	Expression	=COLUMN(AO2)<=$AL$2+40	text	YES
AO2:BG2	Expression	=COLUMN(AO2)<=$AL$2+$AM$2+40	text	YES
AO2:BG2	Expression	=COLUMN(AO2)<=$AL$2+$AM$2+$AN$2+40	text	NO

This is where I am stuck mate.
The is kind of over lapping.
BTW why to use +40 in all?

 

[DanteAmor]

My data starts from the ROW 6.

If number under the title RED says 10, it should make 10 cells RED followed by the other colors.

Or there may be zero.

They are data that you must report. Do not wait for us to solve it, remember, you are asking for help, so you should provide as much information as possible. Although it is very obvious, it is only obvious for you, since you know your data.

Try this:

Sub ColorCell()
  Dim c As Range
  Range("AO6", Cells(Rows.Count, Columns.Count)).Interior.Color = xlNone
  For Each c In Range("AL6", Range("AL" & Rows.Count).End(3))
    If c > 0 Then Range("AO" & c.Row).Resize(, c).Interior.Color = vbRed
    If c.Offset(, 1) > 0 Then Range("AO" & c.Row).Offset(, c).Resize(, c.Offset(, 1)).Interior.Color = vbYellow
    If c.Offset(, 2) > 0 Then Range("AO" & c.Row).Offset(, c + c.Offset(, 1)).Resize(, c.Offset(, 2)).Interior.Color = vbGreen
  Next
End Sub

 

[Crystalyzer]

This is where I am stuck mate.
The is kind of over lapping.
BTW why to use +40 in all?

View attachment 45310

So your order of conditions matters here. The RED should be first, YELLOW second, and GREEN third. Adding 40 to all because I am comparing the column number of the cell to the number indicated for each color. Your data that you want to conditionally format starts in column AL which is column #40

 

[tmagan]

Or there may be zero.

They are data that you must report. Do not wait for us to solve it, remember, you are asking for help, so you should provide as much information as possible. Although it is very obvious, it is only obvious for you, since you know your data.

Try this:

Sub ColorCell()
  Dim c As Range
  Range("AO6", Cells(Rows.Count, Columns.Count)).Interior.Color = xlNone
  For Each c In Range("AL6", Range("AL" & Rows.Count).End(3))
    If c > 0 Then Range("AO" & c.Row).Resize(, c).Interior.Color = vbRed
    If c.Offset(, 1) > 0 Then Range("AO" & c.Row).Offset(, c).Resize(, c.Offset(, 1)).Interior.Color = vbYellow
    If c.Offset(, 2) > 0 Then Range("AO" & c.Row).Offset(, c + c.Offset(, 1)).Resize(, c.Offset(, 2)).Interior.Color = vbGreen
  Next
End Sub

Thank you for you're response. I appriciate that.
As you mentioned, this is not the only way that I am looking or waiting to resolve this issue. Just trying to get more insights from experts who use Excel extensively (NOT me :D coz am a CAD Designer).
Am trying my best aspire and make this KANBAN tool more LEAN for my floor operators.

 

[tmagan]

So your order of conditions matters here. The RED should be first, YELLOW second, and GREEN third. Adding 40 to all because I am comparing the column number of the cell to the number indicated for each color. Your data that you want to conditionally format starts in column AL which is column #40

Hey bud,
As you say, I think the Conditional Formatting is the best and simplest option to handle this.
May be in future on my absents my team should not face difficulties in understanding the VBA CODEs.
This helps great.
Thank you once again all.
Much appreciated.