Conditional formatting using vba

[Sayan2795]

Hi ,
So I created an automated summary sheet which extracts data from multiple sheets and puts it in one workbook. I need to do conditional formatting on the data in summary sheet based on certain parameters

Base	Upper	Lower	PROD A	PROD B	PROD C	PROD D	PROD E
2	4	5	6.1	6.3	7.1	7.4	7.5

The above table shows the basic structure. As it is an automated machine each time the rows and columns will change depending on the number of files from where we take data.

What I need to do :-
Formulae1 = Base + Upper
Formula 2 = Base - Lower

I need to check in a row if the value lies between the two formula ie if Base+ upper > PROD A > Base - lower
If it does it needs to be marked with red.
We have to check for all the products and there are multiple rows with different Base , upper and lower value.

Please help. I need to write a VBA code for this.

 

[Peter_SSs]

See if this does what you want. Test in a copy of your workbook.

Sub UpperLower()
  Dim a As Variant
  Dim i As Long, j As Long, uba2 As Long
  Dim uppr As Double, lowr As Double
  
  With Range("A1").CurrentRegion
    a = .Value
    uba2 = UBound(a, 2)
    For i = 2 To UBound(a)
      uppr = a(i, 1) + a(i, 2)
      lowr = a(i, 1) - a(i, 3)
      For j = 4 To uba2
        If a(i, j) >= lowr Then
          If a(i, j) <= uppr Then
            .Cells(i, j).Interior.Color = vbRed
          End If
        End If
      Next j
    Next i
  End With
End Sub

This is my sample data and results. If it is not what you expect, please give more details.

Book1
	A	B	C	D	E	F	G	H
1	Base	Upper	Lower	PROD A	PROD B	PROD C	PROD D	PROD E
2	2	4	5	6.1	6.3	7.1	7.4	7.5
3	10	3	1	15	12	2	9	12
4	2	4.2	5	6.1	9	0	-2.3	-4
Sheet1

 

[Fazza]

Or using the in-built conditional formatting.
Note I've strictly used the same comparisons as in the question. So does NOT colour entries equal to the limit.
This doesn't have a loop, which should be fast to apply - though might slow the worksheet if you have a huge number of rows.
Maybe not relevant, but using formulas it will be dynamic - if values in columns A:C change.

Sub maybe()

    With Range("A1").CurrentRegion.Offset(, 3).Resize(, 5)
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(RC1+RC2>RC,RC>RC1-RC3)"
        .FormatConditions(1).Interior.ColorIndex = 3
    End With

End Sub

 

[Sayan2795]

I am getting a type mismatch error in line uppr = a(I,1) +a(I,3)

 

[Peter_SSs]

I am getting a type mismatch error in line uppr = a(I,1) +a(I,3)

When there is more than 1 respondent in the thread, it is a good idea to make it clear who you are addressing.
That actually isn't even a line in my code but never-the-less the error you are reporting could indicate that at least some of the "numbers" in columns A:C are not straight-forward numbers. There may be error values or text characters that perhaps you cannot see.
Could something like that be possible?

Does your table actually start in column A?

If you start a brand new workbook and manually type in the sample data the same as mine and put the code in that workbook and run it what happens?