Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format row based on value
Hello,
I have this code that look at the values in column 2 for each row specified in the code (2 to 9216). I want it to colour the row everytime the value changes. This does this perfectly except it doesnt colour the row then the value first changes. So for example if row 2 to 6 are the same and row 7 to 10 are the same, rows 8 to 10 are formatted. Can anyone help with this? Dim lRow As Long Dim lCol As Long For lRow = 2 To 9216 If Cells(lRow, 2).Value = Cells(lRow - 1, 2) Then If IsEmpty(Cells(lRow, 256)) Then lCol = Cells(lRow, 256).End(xlToLeft).Column Else lCol = 256 End If Range(Cells(lRow, 1), Cells(lRow, lCol)).Interior.ColorIndex = 36 End If Next This is a repost from earlier today, I hope I have worded it better! Martin |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format row based on value
Hi,
Thanks, I see what you mean however the formatting needs to take place over 10,000 lines and row 2 will have no relevance to say row 1,500. What I need is to say by code for example is if current IRow, column 2 is different to the row above, column 2 then do the formatting "Nigel" wrote: A quick fix - change the first If statement as follows..... If Cells(lRow, 2).Value = Cells(lRow - 1, 2) Or lRow = 2 Then -- Regards, Nigel "Martin" wrote in message ... Hello, I have this code that look at the values in column 2 for each row specified in the code (2 to 9216). I want it to colour the row everytime the value changes. This does this perfectly except it doesnt colour the row then the value first changes. So for example if row 2 to 6 are the same and row 7 to 10 are the same, rows 8 to 10 are formatted. Can anyone help with this? Dim lRow As Long Dim lCol As Long For lRow = 2 To 9216 If Cells(lRow, 2).Value = Cells(lRow - 1, 2) Then If IsEmpty(Cells(lRow, 256)) Then lCol = Cells(lRow, 256).End(xlToLeft).Column Else lCol = 256 End If Range(Cells(lRow, 1), Cells(lRow, lCol)).Interior.ColorIndex = 36 End If Next This is a repost from earlier today, I hope I have worded it better! Martin |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format row based on value
You need to explain what you are trying to achieve? As I see it coloring
the first row of each group will color all rows! Or do you have blank rows you do not wish to color? -- Regards, Nigel "Martin" wrote in message ... Hi, Thanks, I see what you mean however the formatting needs to take place over 10,000 lines and row 2 will have no relevance to say row 1,500. What I need is to say by code for example is if current IRow, column 2 is different to the row above, column 2 then do the formatting "Nigel" wrote: A quick fix - change the first If statement as follows..... If Cells(lRow, 2).Value = Cells(lRow - 1, 2) Or lRow = 2 Then -- Regards, Nigel "Martin" wrote in message ... Hello, I have this code that look at the values in column 2 for each row specified in the code (2 to 9216). I want it to colour the row everytime the value changes. This does this perfectly except it doesnt colour the row then the value first changes. So for example if row 2 to 6 are the same and row 7 to 10 are the same, rows 8 to 10 are formatted. Can anyone help with this? Dim lRow As Long Dim lCol As Long For lRow = 2 To 9216 If Cells(lRow, 2).Value = Cells(lRow - 1, 2) Then If IsEmpty(Cells(lRow, 256)) Then lCol = Cells(lRow, 256).End(xlToLeft).Column Else lCol = 256 End If Range(Cells(lRow, 1), Cells(lRow, lCol)).Interior.ColorIndex = 36 End If Next This is a repost from earlier today, I hope I have worded it better! Martin |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format row based on value
Hi,
Here is a sample of the data I have: Column Heading Area 1 Area 1 Area 1 Area 2 Area 2 Area 2 Area 2 Area 3 Area 3 Area 3 Area 3 I want to leave the rows with the value of "Area 1" as normal. If the row then changes to another name, in this example "Area 2" then format the rows yellow. If the value changes again, in this example "Area 3" then leave the rows as normal and so on and so on. The code I posted below does this almost perfectly but does not colour the first row when the change takes place i.e. the first row with "Area 2". "Nigel" wrote: You need to explain what you are trying to achieve? As I see it coloring the first row of each group will color all rows! Or do you have blank rows you do not wish to color? -- Regards, Nigel "Martin" wrote in message ... Hi, Thanks, I see what you mean however the formatting needs to take place over 10,000 lines and row 2 will have no relevance to say row 1,500. What I need is to say by code for example is if current IRow, column 2 is different to the row above, column 2 then do the formatting "Nigel" wrote: A quick fix - change the first If statement as follows..... If Cells(lRow, 2).Value = Cells(lRow - 1, 2) Or lRow = 2 Then -- Regards, Nigel "Martin" wrote in message ... Hello, I have this code that look at the values in column 2 for each row specified in the code (2 to 9216). I want it to colour the row everytime the value changes. This does this perfectly except it doesnt colour the row then the value first changes. So for example if row 2 to 6 are the same and row 7 to 10 are the same, rows 8 to 10 are formatted. Can anyone help with this? Dim lRow As Long Dim lCol As Long For lRow = 2 To 9216 If Cells(lRow, 2).Value = Cells(lRow - 1, 2) Then If IsEmpty(Cells(lRow, 256)) Then lCol = Cells(lRow, 256).End(xlToLeft).Column Else lCol = 256 End If Range(Cells(lRow, 1), Cells(lRow, lCol)).Interior.ColorIndex = 36 End If Next This is a repost from earlier today, I hope I have worded it better! Martin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditiona format based on format of another cell | Excel Worksheet Functions | |||
Setting cell format based on format codes | Excel Programming | |||
If / Then based off conditional format | Excel Programming | |||
how do i format a cell based on format of a range of cells? | Excel Discussion (Misc queries) | |||
Number format based on number format of another cell in another workbook | Excel Programming |