Color rows according to a specific column and a specific cell

**k0st4din** · Yesterday, 07:47 AM

Hello everyone,
I have been using this macro for many years (Thanks to Paul_Hossler )

(link to old thread -
http://www.vbaexpress.com/forum/showthread.php?70692-Delete-all-conditional-formatting-and-coloring-conditional-on-specific-rows&p=419047&viewfull=1#post419047

which works on the principle of searching and comparing a certain column (and the values in it) and according to the numbers placed at the end of the table, if there is an increase, it colors the given cells in the colors I have specified.

Due to some necessary changes in the recalculation, I am asking for help and assistance on how to rework it so that it does absolutely the same coloring, but here comes the problem:
Can it be done so that I can enter in the macro itself exactly from which column and exactly from which row the information should be taken and still do the coloring.
One type in the specified range, to be able to do the calculations again, but one type (as if) each column and selected row is independent.

The idea is to be able to write in the macro exactly which column to start the comparison from and exactly which row.
For example, column EM and row 10, column EO and row 5, and so on, I write for each needed row and branch if there are numbers at the end of the table.

Option Explicit

Sub DoAllRows_2025_22()
    Dim r As Long    
    Application.ScreenUpdating = False    
    With ActiveSheet
        With .Range("DO3:EZ78") 'tova e diapazona v koti trqbva da se iztriqt vsichki condittional
            .FormatConditions.Delete        
            .Interior.ColorIndex = xlColorIndexNone        
            ' clear any empty, but text i.e. 0 length strings
            Call .Replace(vbNullString, "###ZZZ###", LookAt:=xlWhole)
            Call .Replace("###ZZZ###", vbNullString, LookAt:=xlWhole)        
            ' clear the settings
            .Find What:=vbNullString, After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, _
                   SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False
            .Replace What:=vbNullString, Replacement:=vbNullString, ReplaceFormat:=False
        End With        
        '   https://www.rondebruin.nl/win/s9/win012.htm
        '   Excel 97 = 8
        '   Excel 2000 = 9
        '   Excel 2002 = 10
        '   Excel 2003 = 11
        '   Excel 2007 = 12
        '   Excel 2010 = 14
        '   Excel 2013 = 15
        '   Excel 2016 = 16
        '   Excel 2019 and Excel 365 also give you number 16
        If Val(Application.Version) > 12 Then 'If Application.Version > 12 Then
            For r = 3 To 78 'ot tuk sa redovete ot 3ti red do 78ti red
                Call AddCF(r)
            Next r
        Else
            For r = 3 To 79 'ot tuk sa redovete ot 3ti red do 79-Vi red
                Call AddInteriorColor(r)
            Next r
        End If
    End With
    Application.ScreenUpdating = True
End Sub


Private Sub AddInteriorColor(rowNum As Long)
    Dim T1 As Long, T2 As Long, T3 As Long, T4 As Long, T5 As Long, T6 As Long, T7 As Long, T8 As Long, T9 As Long, T10 As Long, T0 As Long
    Dim r As Range
    Dim c As Long    
    Set r = ActiveSheet.Rows(rowNum)    
    With r
        If .Cells(1, 157).Value = 0 Then Exit Sub    '   no threshholds 15ti red e pyrviqt bonus ili kolonata s godinata 2023(EA) naprimer    
        T0 = .Cells(1, 117).Value 'DM KOLONA ILI SRAVNENIETO SPRQMO 1.12.2022    
        T1 = .Cells(1, 157).Value 'tezi 131, 132, 133, 134 ili EA, EB, EC, ED sa bonusite +30,+60,+90,+120 naprimer
        T2 = .Cells(1, 158).Value
        T3 = .Cells(1, 159).Value
        T4 = .Cells(1, 160).Value
        T5 = .Cells(1, 161).Value 'new color
        T6 = .Cells(1, 162).Value 'new color
        T7 = .Cells(1, 163).Value 'new color
        T8 = .Cells(1, 164).Value 'new color
        T9 = .Cells(1, 165).Value 'new color
        T10 = .Cells(1, 166).Value 'new color    
        Set r = r.Cells(1, 119).Resize(1, 38) 'tuk rcells oznachava ot koq kolona da zapochne da izchislqva ili inache ot 1.1.2023 (DO), a resize 12 oznachava kolko nadqsno koloni
    End With
    With r
        For c = 1 To 38     '   r starts in col C tuk syshto promenqme tow 25 e naprimer ot 1.2023 do 12.2024, t.e 25 reda nadqsno
            If .Cells(1, c).Value >= T0 + T10 Then
                .Cells(1, c).Interior.Color = rgbSpringGreen 'new color rgbPowderBlue
            ElseIf .Cells(1, c).Value >= T0 + T9 Then
                .Cells(1, c).Interior.Color = rgbOrchid 'new color
            ElseIf .Cells(1, c).Value >= T0 + T8 Then
                .Cells(1, c).Interior.Color = rgbOlive 'new color
            ElseIf .Cells(1, c).Value >= T0 + T7 Then
                .Cells(1, c).Interior.Color = rgbPowderBlue 'new color
            ElseIf .Cells(1, c).Value >= T0 + T6 Then
                .Cells(1, c).Interior.Color = vbBlue 'new color
            ElseIf .Cells(1, c).Value >= T0 + T5 Then
                .Cells(1, c).Interior.Color = vbGreen 'new color
            ElseIf .Cells(1, c).Value >= T0 + T4 Then
                .Cells(1, c).Interior.Color = vbRed
            ElseIf .Cells(1, c).Value >= T0 + T3 Then
                .Cells(1, c).Interior.Color = vbMagenta
            ElseIf .Cells(1, c).Value >= T0 + T2 Then
                .Cells(1, c).Interior.Color = vbCyan
            ElseIf .Cells(1, c).Value >= T0 + T1 Then
                .Cells(1, c).Interior.Color = vbYellow
            End If
         Next c
    End With
End Sub



Private Sub AddCF(rowNum As Long)
    Dim T1 As Long, T2 As Long, T3 As Long, T4 As Long, T5 As Long, T6 As Long, T7 As Long, T8 As Long, T9 As Long, T10 As Long
    Dim CFormula As String
    Dim r As Range    
    Set r = ActiveSheet.Rows(rowNum)    
    With r
        If .Cells(1, 157).Value = 0 Then Exit Sub    '   no threshholds 15ti red e pyrviqt bonus ili kolonata s godinata 2023 naprimer    
        T1 = .Cells(1, 157).Value 'tezi 131, 132, 133, 134 ili EA, EB, EC, ED sa bonusite +30,+60,+90,+120 naprimer
        T2 = .Cells(1, 158).Value
        T3 = .Cells(1, 159).Value
        T4 = .Cells(1, 160).Value
        T5 = .Cells(1, 161).Value 'new color
        T6 = .Cells(1, 162).Value 'new color
        T7 = .Cells(1, 163).Value 'new color
        T8 = .Cells(1, 164).Value 'new color
        T9 = .Cells(1, 165).Value 'new color
        T10 = .Cells(1, 166).Value 'new color    
        CFormula = "=$DM" & .Cells(1, 1).Row & "+" 'tuk $DM, se promenq na bukvata(kolonata), koqto ni e za sravnenie, naprimer $DM sprqmo 12 mesec na minalata godina    
        Set r = r.Cells(1, 119).Resize(1, 38) 'tuk rcells oznachava ot koq kolona da zapochne da izchislqva ili inache ot 1.1.2023, a resize 12 oznachava kolko nadqsno koloni
    End With
    With r
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=CFormula & T10
        .FormatConditions(.FormatConditions.Count).Interior.Color = rgbSpringGreen 'new color
        .FormatConditions(.FormatConditions.Count).StopIfTrue = True
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=CFormula & T9
        .FormatConditions(.FormatConditions.Count).Interior.Color = rgbOrchid 'new color
        .FormatConditions(.FormatConditions.Count).StopIfTrue = True
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=CFormula & T8
        .FormatConditions(.FormatConditions.Count).Interior.Color = rgbOlive 'new color rgbPowderBlue
        .FormatConditions(.FormatConditions.Count).StopIfTrue = True
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=CFormula & T7
        .FormatConditions(.FormatConditions.Count).Interior.Color = rgbPowderBlue 'new color rgbPowderBlue
        .FormatConditions(.FormatConditions.Count).StopIfTrue = True
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=CFormula & T6
        .FormatConditions(.FormatConditions.Count).Interior.Color = vbBlue 'new color
        .FormatConditions(.FormatConditions.Count).StopIfTrue = True
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=CFormula & T5
        .FormatConditions(.FormatConditions.Count).Interior.Color = vbGreen 'new color
        .FormatConditions(.FormatConditions.Count).StopIfTrue = True
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=CFormula & T4
        .FormatConditions(.FormatConditions.Count).Interior.Color = vbRed
        .FormatConditions(.FormatConditions.Count).StopIfTrue = True
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=CFormula & T3
        .FormatConditions(.FormatConditions.Count).Interior.Color = vbMagenta
        .FormatConditions(.FormatConditions.Count).StopIfTrue = True
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=CFormula & T2
        .FormatConditions(.FormatConditions.Count).Interior.Color = vbCyan
        .FormatConditions(.FormatConditions.Count).StopIfTrue = True
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=CFormula & T1
        .FormatConditions(.FormatConditions.Count).Interior.Color = vbYellow
        .FormatConditions(.FormatConditions.Count).StopIfTrue = True
    End With
End Sub

**Paul_Hossler** · Yesterday, 09:51 AM

I'm sure it's doable, BUT please attach a workbook and explain with examples

Can it be done so that I can enter in the macro itself exactly from which column and exactly from which row the information should be taken and still do the coloring.
One type in the specified range, to be able to do the calculations again, but one type (as if) each column and selected row is independent.

The idea is to be able to write in the macro exactly which column to start the comparison from and exactly which row.
For example, column EM and row 10, column EO and row 5, and so on, I write for each needed row and branch if there are numbers at the end of the table.

Originally the test was against Col A in each row

Capture.JPG

**k0st4din** · Today, 12:54 AM

Hello Paul_Hossler,
I'm very glad that we're writing again.
So, let me start like this - in the tests we did then for the macro above, we really started from column A, but over the years the table grew and for each subsequent year, I changed where it should start checking and making comparisons and then coloring the cells.
So whether it will be column A or some other EO, EM, AZ doesn't matter. It starts from a selected column and checks each row, where at the end of the table there are set values and starts coloring, if there are no values at the end of the table it skips the row and does so up to and in the range that is set. No matter if it is 2 or 200 rows.
The macro simply works super fantastically and correctly.
Now, however, since there are some changes, I need to do the same thing again, but to be able to record exactly from which column and which row to start checking to the right and if it matches the given amount to color the cells.
In the example, I have given a small part of the table and the idea is - from where it is brown, from there to start checking to the right. I record the initial brown cells, and in the example you will see all of them. S5, Q6, M8, T9, etc. to where the range is defined (as in the other macro)
This as a postscript - Yes, we will do tests, again from column A, but in my table they will be completely different.
Thank you very much and I remain at your disposal!

P.S. - And one more thing, is it possible for the columns not to be with numbers but with their letters?

**Paul_Hossler** · Today, 07:01 AM

What are the numbers in AC:AK?

What is columns 117, 157, ...

        T0 = .Cells(1, 117).Value 'DM KOLONA ILI SRAVNENIETO SPRQMO 1.12.2022        T1 = .Cells(1, 157).Value 'tezi 131, 132, 133, 134 ili EA, EB, EC, ED sa bonusite +30,+60,+90,+120 naprimer
        T2 = .Cells(1, 158).Value
        T3 = .Cells(1, 159).Value
        T4 = .Cells(1, 160).Value
        T5 = .Cells(1, 161).Value 'new color
        T6 = .Cells(1, 162).Value 'new color
        T7 = .Cells(1, 163).Value 'new color
        T8 = .Cells(1, 164).Value 'new color
        T9 = .Cells(1, 165).Value 'new color
        T10 = .Cells(1, 166).Value 'new color

**k0st4din** · Today, 08:03 AM

Hello,
the code you wrote a long time ago and to this day it works great, for which I am infinitely grateful.
Now these numbers I don't know why you decided then that instead of the names, i.e. the letters of the columns should be numbers (for example column A is 1 and so on to the end, as if you were using R1C1 REFERENCE style), I have no idea why it was done that way, but to this day I count my columns that way.
That's why I asked you in the postscript if it could be with letters, not numbers, because it is very laborious to count the columns.
In this case, this 117 (in the test table it should be 1) is the column from which it is compared (in this macro yours, and the rest from 158 to 166-> are also columns, are the colors that change according to the values that are at the end of the table from AB to AK (and in the test table, respectively, these numbers are from 28 to 37).
But all this in the macro and for the old calculation, which made a comparison in one column and in all rows (in each row).

**Paul_Hossler** · Today, 08:52 AM

You'll have to take me through it again

Using S5 = 0 as example

Q1 - What do you test against

Q2 - What color(s)

Q3 - Col S is the 4th month so is AF5 the test somehow? Just seems like AC:AK are MUCH larger that any of the data in P:AA

Capture.JPG

Q4 - Where did the 50 come from in AB5?

Q5 - Why are AC:AK rows different? Increasing colum I can guess, but why do the rows change within a column?

**k0st4din** · Today, 09:15 AM

So,
S4 in this case is empty, but it still checks if there are any numbers from the next cell T5 (i.e. the 5th month, and until the end i.e. the 12th month cell AA).
If there are none, it does not color, if there are, it looks at whether they are in a given range to determine the color or in other words - each subsequent month is checked according to the desired cell (in brown) and if they are between 50 and 99 it should be in yellow, if they are 100 to 149 it should be in blue and so on until the end. That is, as in the macro >=.
You ask where these 50 came from, etc. - from there I set how many and after which value to change the color.
That is my problem, because for each row there is a different month (cell) from where I have to start tracking, when numbers have accumulated and it has to color the cells in a given color.
The next row in the example interests me to start checking from Q4 to the end (i.e. up to 12.2025 inclusive)
The next row 7 currently does not have any values in the AB:AK range (it can check it, but it will not show anything, at least for the time being, if in time it decides to have some values that will be entered in AB:AK, then it will calculate and color it.
The next row and column is M8 and from there to the right it starts checking.
I have chosen the colors and they are on line 1 from P to Y and in the macro itself they are defined here

Private Sub AddInteriorColor(rowNum As Long)    Dim T1 As Long, T2 As Long, T3 As Long, T4 As Long, T5 As Long, T6 As Long, T7 As Long, T8 As Long, T9 As Long, T10 As Long, T0 As Long
    Dim r As Range
    Dim c As Long    
    Set r = ActiveSheet.Rows(rowNum)    
    With r
        If .Cells(1, 157).Value = 0 Then Exit Sub    '   no threshholds 15ti red e pyrviqt bonus ili kolonata s godinata 2023(EA) naprimer    
        T0 = .Cells(1, 117).Value 'DM KOLONA ILI SRAVNENIETO SPRQMO 1.12.2022    
        T1 = .Cells(1, 157).Value 'tezi 131, 132, 133, 134 ili EA, EB, EC, ED sa bonusite +30,+60,+90,+120 naprimer
        T2 = .Cells(1, 158).Value
        T3 = .Cells(1, 159).Value
        T4 = .Cells(1, 160).Value
        T5 = .Cells(1, 161).Value 'new color
        T6 = .Cells(1, 162).Value 'new color
        T7 = .Cells(1, 163).Value 'new color
        T8 = .Cells(1, 164).Value 'new color
        T9 = .Cells(1, 165).Value 'new color
        T10 = .Cells(1, 166).Value 'new color    
        Set r = r.Cells(1, 119).Resize(1, 38) 'tuk rcells oznachava ot koq kolona da zapochne da izchislqva ili inache ot 1.1.2023 (DO), a resize 12 oznachava kolko nadqsno koloni
    End With
    With r
        For c = 1 To 38     '   r starts in col C tuk syshto promenqme tow 25 e naprimer ot 1.2023 do 12.2024, t.e 25 reda nadqsno
            If .Cells(1, c).Value >= T0 + T10 Then
                .Cells(1, c).Interior.Color = rgbSpringGreen 'new color rgbPowderBlue
           ElseIf .Cells(1, c).Value >= T0 + T9 Then
                .Cells(1, c).Interior.Color = rgbOrchid 'new color
           ElseIf .Cells(1, c).Value >= T0 + T8 Then
                .Cells(1, c).Interior.Color = rgbOlive 'new color
            ElseIf .Cells(1, c).Value >= T0 + T7 Then
                .Cells(1, c).Interior.Color = rgbPowderBlue 'new color
            ElseIf .Cells(1, c).Value >= T0 + T6 Then
                .Cells(1, c).Interior.Color = vbBlue 'new color
            ElseIf .Cells(1, c).Value >= T0 + T5 Then
                .Cells(1, c).Interior.Color = vbGreen 'new color
            ElseIf .Cells(1, c).Value >= T0 + T4 Then
                .Cells(1, c).Interior.Color = vbRed
            ElseIf .Cells(1, c).Value >= T0 + T3 Then
                .Cells(1, c).Interior.Color = vbMagenta
            ElseIf .Cells(1, c).Value >= T0 + T2 Then
                .Cells(1, c).Interior.Color = vbCyan
            ElseIf .Cells(1, c).Value >= T0 + T1 Then
                .Cells(1, c).Interior.Color = vbYellow
            End If
         Next c
    End With
End Sub



Private Sub AddCF(rowNum As Long)
    Dim T1 As Long, T2 As Long, T3 As Long, T4 As Long, T5 As Long, T6 As Long, T7 As Long, T8 As Long, T9 As Long, T10 As Long
    Dim CFormula As String
    Dim r As Range    
    Set r = ActiveSheet.Rows(rowNum)    
    With r
        If .Cells(1, 157).Value = 0 Then Exit Sub    '   no threshholds 15ti red e pyrviqt bonus ili kolonata s godinata 2023 naprimer    
        T1 = .Cells(1, 157).Value 'tezi 131, 132, 133, 134 ili EA, EB, EC, ED sa bonusite +30,+60,+90,+120 naprimer
        T2 = .Cells(1, 158).Value
        T3 = .Cells(1, 159).Value
        T4 = .Cells(1, 160).Value
        T5 = .Cells(1, 161).Value 'new color
        T6 = .Cells(1, 162).Value 'new color
        T7 = .Cells(1, 163).Value 'new color
        T8 = .Cells(1, 164).Value 'new color
        T9 = .Cells(1, 165).Value 'new color
        T10 = .Cells(1, 166).Value 'new color    
        CFormula = "=$DM" & .Cells(1, 1).Row & "+" 'tuk $DM, se promenq na bukvata(kolonata), koqto ni e za sravnenie, naprimer $DM sprqmo 12 mesec na minalata godina    
        Set r = r.Cells(1, 119).Resize(1, 38) 'tuk rcells oznachava ot koq kolona da zapochne da izchislqva ili inache ot 1.1.2023, a resize 12 oznachava kolko nadqsno koloni
    End With
    With r
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=CFormula & T10
        .FormatConditions(.FormatConditions.Count).Interior.Color = rgbSpringGreen 'new color
        .FormatConditions(.FormatConditions.Count).StopIfTrue = True
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=CFormula & T9
        .FormatConditions(.FormatConditions.Count).Interior.Color = rgbOrchid 'new color
        .FormatConditions(.FormatConditions.Count).StopIfTrue = True
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=CFormula & T8
        .FormatConditions(.FormatConditions.Count).Interior.Color = rgbOlive 'new color rgbPowderBlue
        .FormatConditions(.FormatConditions.Count).StopIfTrue = True
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=CFormula & T7
        .FormatConditions(.FormatConditions.Count).Interior.Color = rgbPowderBlue 'new color rgbPowderBlue
        .FormatConditions(.FormatConditions.Count).StopIfTrue = True
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=CFormula & T6
        .FormatConditions(.FormatConditions.Count).Interior.Color = vbBlue 'new color
        .FormatConditions(.FormatConditions.Count).StopIfTrue = True
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=CFormula & T5
        .FormatConditions(.FormatConditions.Count).Interior.Color = vbGreen 'new color
        .FormatConditions(.FormatConditions.Count).StopIfTrue = True
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=CFormula & T4
        .FormatConditions(.FormatConditions.Count).Interior.Color = vbRed
        .FormatConditions(.FormatConditions.Count).StopIfTrue = True
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=CFormula & T3
        .FormatConditions(.FormatConditions.Count).Interior.Color = vbMagenta
        .FormatConditions(.FormatConditions.Count).StopIfTrue = True
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=CFormula & T2
        .FormatConditions(.FormatConditions.Count).Interior.Color = vbCyan
        .FormatConditions(.FormatConditions.Count).StopIfTrue = True
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=CFormula & T1
        .FormatConditions(.FormatConditions.Count).Interior.Color = vbYellow
        .FormatConditions(.FormatConditions.Count).StopIfTrue = True     End With

And it is very important that it can also work with older versions of the office suite, as it did in the old macro.

  End With                '   https://www.rondebruin.nl/win/s9/win012.htm
        '   Excel 97 = 8
        '   Excel 2000 = 9
        '   Excel 2002 = 10
        '   Excel 2003 = 11
        '   Excel 2007 = 12
        '   Excel 2010 = 14
        '   Excel 2013 = 15
        '   Excel 2016 = 16
        '   Excel 2019 and Excel 365 also give you number 16
        If Val(Application.Version) > 12 Then 'If Application.Version > 12 Then
            For r = 3 To 78 'ot tuk sa redovete ot 3ti red do 78ti red                 
Call AddCF(r)

Maybe I should add something else and why I'm having trouble.
The idea is that in a given month there are some changes in the prices of certain products.
If I have any change, I decrease or increase the numbers in AB:AK for each row where necessary.
For this reason, I'm looking for a way to start calculating, coloring from the cell I set and to the right until the end of the year.
When the next year comes, I will decide whether to change the cell or it will remain the same and the macro will move forward and do the check for the next year as well.

**Paul_Hossler** · Today, 09:39 AM

What about the rest of the number, P5, P6, etc. thatwere not gray?

So

1. Check each cell in P3:AA37 (e.g. S5)

Using row 5 as example

P5 = 1, Q5 is blank so no color for P5

Q5 is blank, but R5 = 3 so no color for Q5 since 3 < AB5 - 1

R5 = 3, but S5 is blank so no color for R5

S5 is blank, but T5 = 2 so no color for S5 since 3 < AB5 - 1

T5 = 2, but U5 is blank so no color for T5

What do you mean accumlated?

These cells in row 1 are blank in your example. How do the factor in?

        If .Cells(1, 157).Value = 0 Then Exit Sub    '   no threshholds 15ti red e pyrviqt bonus ili kolonata s godinata 2023(EA) naprimer    
        T0 = .Cells(1, 117).Value 'DM KOLONA ILI SRAVNENIETO SPRQMO 1.12.2022    
        T1 = .Cells(1, 157).Value 'tezi 131, 132, 133, 134 ili EA, EB, EC, ED sa bonusite +30,+60,+90,+120 naprimer
        T2 = .Cells(1, 158).Value
        T3 = .Cells(1, 159).Value
        T4 = .Cells(1, 160).Value
        T5 = .Cells(1, 161).Value 'new color
        T6 = .Cells(1, 162).Value 'new color
        T7 = .Cells(1, 163).Value 'new color
        T8 = .Cells(1, 164).Value 'new color
        T9 = .Cells(1, 165).Value 'new color
        T10 = .Cells(1, 166).Value 'new color

**k0st4din** · Today, 10:06 AM

No, no, not backwards but from the brown color (or gray) I don't know how you see them on your computer.
The check should start from the brown cell To the Right, not before it.
In the example you give, the numbers are very small and will not be updated until they reach the numbers I wrote in the range AB:AK.
If from the colored cell to the right let it be this row P, the check should start from S5 to the right and if for example in the cell T5 there are 50 to 99 (i.e. >=) it should be yellow.
If the cell is empty, it does not color anything (maybe there was no product and it skips a given cell on the same row.
There is no range, this ->> 1. Check each cell in P3:AA37 (e.g. S5)
this is not the case - if you mean from S5 to do the check everywhere.
I can tell the macro to check the specific row from D5 to the end of the table without taking into account AB:AK in this example.
In the previous comment I wrote that I copied the macro from my workbook, in this test book the range is AB:AK

**Paul_Hossler** · Today, 10:58 AM

Sorry to be dense, but ...

What are the colors in P1:Y1 for?

Why the brown cell? There's lots of others with data that are not brown

Only check from a brown cell to the right?

So S5 is empty and T5 = 2 < 50 so leave S5 alone and no color for T5?

What color should P25:t25 be (I hanged some numbers) and how did the logic work?

91 < 100 so no color

0 so no color

130 > 100 and < 200 so magenta ????

349 > 300 and < 400 so red?

222 > 200 and < 300 so green???

If I wanted to raise the abstraction level ...

1. row specific set of 10 numbers each mapping to a color

2. for each non-blank cell in the data, use the row-specific ranges to get the color for that cell

**Paul_Hossler** · Today, 11:34 AM

Going way out a limb aqnd take a WAG ...

see if this is close.

It's my best guess and what you're wanting to do

**k0st4din** · Today, 01:45 PM

Hello, I just tried the table but it's not like that. That's why I'm uploading another table in which I've shown (figuratively speaking, real numbers) how things and colors should happen.
This test checks absolutely the entire range, and I said at the beginning that the check should start from the cells that I colored in brown (at least for me it's brown).
The other thing is that in the macro, I don't see anywhere (and this is most likely because it's not working correctly) where I personally can write exactly from which cell to the right it should start calculating.
In the table that I'm going to upload, please take a look (because I didn't do it for all rows), but these: S5 - and to the right after that, Q6- and to the right after that, M8 - and to the right after that, T9- and to the right after that, P10- and to the right after that, A34 - and to the right after that.
I made them colored up to a maximum of 4 colors so as not to make them all 10 colors, but the logic is clear.
The question is, somewhere in the macro, I can write down exactly which cell in the row to start calculating and coloring, and not have everyone start from column A. This first
Secondly, there is no need for the cell from which the comparison is made to be colored in a certain color

You say it takes it from the brown cell to the right, but in your macro it doesn't do it correctly.
For example: It should start from N12 - to the right, and it colors the entire row from A12, where it meets the requirement and reaches the specific numbers, for N16- it's also not true, again it colors the entire row. From C16 to the right.

"What are the colors in P1:Y1 for?"
The colors are just for my help and convenience, they don't do any other work. Kind of like Notes (hint, reminder)

Maybe I'm confusing you something. This brown cell is just for reference, from where exactly on a specific row the calculation and coloring should start. This is missing in the macro and I won't be able to say where to start checking. The idea is not to capture the entire range and check everywhere. I need to be able to record in the macro from where, to where.

**Paul_Hossler** · Today, 03:14 PM

It's not brown in my palette, and I didn't realize that you manually marked them brown

This is version better (I think) but check some of your colors you sent. I think you missed some when I compared the macro against your workbook

Screen shot is marco

Capture.JPG

Thread: Color rows according to a specific column and a specific cell

Thread Tools

Display

Color rows according to a specific column and a specific cell

Posting Permissions