excelnoob001
New Member
- Joined
- Jul 24, 2019
- Messages
- 26
I am completely stuck on this one.
I have twenty seven columns I wish to format, however I feel conditional formatting might slow down my workbook.
There are three columns per sheet, with a total of nine sheets. They all start in the same range (C7 to E-whatever).
For each column, I wish to have a comparison that would check whether or not that cell is 3x greater than the cell below it, or if the cell below is 3x greater than the active cell. (In this beginning it's C7)
If either of those comparisons are true, highlight the text 1 and 2 units to the left of the active cell and the cell below to purple.
Any help or insight is greatly appreciated.
Thank you.
I have twenty seven columns I wish to format, however I feel conditional formatting might slow down my workbook.
There are three columns per sheet, with a total of nine sheets. They all start in the same range (C7 to E-whatever).
For each column, I wish to have a comparison that would check whether or not that cell is 3x greater than the cell below it, or if the cell below is 3x greater than the active cell. (In this beginning it's C7)
If either of those comparisons are true, highlight the text 1 and 2 units to the left of the active cell and the cell below to purple.
Rich (BB code):
Public Sub errorCheck3()
Dim firstIndex, secondIndex As Integer
Dim firstCell, secondCell As Range
firstIndex = 7
secondIndex = 8
Set firstCell = Range("C" & firstIndex) 'C7
Set secondCell = Range("C" & secondIndex) 'C8
Application.Calculation = xlCalculationManual
While firstCell <> "" And secondCell <> "" 'while neither cells r blank
Set firstCell = Range("C" & firstIndex)
Set secondCell = Range("C" & secondIndex)
If firstCell / secondCell >= 3 Then 'if first cell is 3 times greater than second cell
firstCell.Offset(0, 2).Font.Color = vbPurple
firstCell.Offset(0, 1).Font.Color = vbPurple
firstCell.Offset(1, 2).Font.Color = vbPurple
firstCell.Offset(1, 1).Font.Color = vbPurple
ElseIf secondCell / firstCell >= 3 Then 'if second cell is 3 times greater than first cell
firstCell.Offset(0, 2).Font.Color = vbPurple
firstCell.Offset(0, 1).Font.Color = vbPurple
firstCell.Offset(1, 2).Font.Color = vbPurple
firstCell.Offset(1, 1).Font.Color = vbPurple
End If
firstIndex = firstIndex + 2 'C6 now is C8
secondIndex = secondIndex + 2 'C7 is now C9
Wend
Application.Calculation = xlCalculationAutomatic
MsgBox "Error Checking Successful."
End Sub
Any help or insight is greatly appreciated.
Thank you.
Last edited: