Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculation based on row and column values
I have the above vba, which loops thru rows b9 to 383 and checks th cell values, colouring the cells in G depending upon the result. My question is this = at the moment this is only usable for checking against BF. What I would like is for it to go from bF to CJ and chec each row value against row 385 for each row. Thanks for help anyone. Sub TrafficLights() Dim R As Integer Dim Pcent As Integer Pcent = 0.05 For R = 9 To 383 ' note the number range relates to the rows If Range("BF" & R).Value < (Range("bf385").Value + Pcent) Then Range("bf" & R).Interior.Color = vbGreen Else Range("bf" & R).Interior.Color = vbRed End If Next R End Sub End Su -- AmyTaylo ----------------------------------------------------------------------- AmyTaylor's Profile: http://www.excelforum.com/member.php...fo&userid=2097 View this thread: http://www.excelforum.com/showthread.php?threadid=38841 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculation based on row and column values
AmyTaylor
you could try something like this: Sub TrafficLights2() Dim iRow As Integer Dim iColumn As Integer Dim Pcent As Integer Pcent = 0.05 Application.ScreenUpdating = False For iColumn = 58 To 88 ' number range relates to columns BF to CJ For iRow = 9 To 383 ' note the number range relates to the rows If Cells(iRow, iColumn).Value _ < Cells(385, iColumn).Value + Pcent Then Cells(iRow, iColumn).Interior.Color = vbGreen Else Cells(iRow, iColumn).Interior.Color = vbRed End If Next iRow Next iColumn Application.ScreenUpdating = True End Sub But I think you'd be far better using Conditional Formatting. Regards Trevor "AmyTaylor" wrote in message ... I have the above vba, which loops thru rows b9 to 383 and checks the cell values, colouring the cells in G depending upon the result. My question is this = at the moment this is only usable for checking B against BF. What I would like is for it to go from bF to CJ and check each row value against row 385 for each row. Thanks for help anyone. Sub TrafficLights() Dim R As Integer Dim Pcent As Integer Pcent = 0.05 For R = 9 To 383 ' note the number range relates to the rows If Range("BF" & R).Value < (Range("bf385").Value + Pcent) Then Range("bf" & R).Interior.Color = vbGreen Else Range("bf" & R).Interior.Color = vbRed End If Next R End Sub End Sub -- AmyTaylor ------------------------------------------------------------------------ AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970 View this thread: http://www.excelforum.com/showthread...hreadid=388411 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to do a calculation based on the values in two cells | Excel Worksheet Functions | |||
Calculation based on Y or N Values | Excel Discussion (Misc queries) | |||
Adding numerical values based on multiple values in another column | Excel Worksheet Functions | |||
how to substitute a value for calculation with a column of values | Excel Worksheet Functions | |||
Calculating values to column D with formula based on values column A | Excel Programming |