Macro, formula help needed,, please
I have no idea how to explain this but I am looking to find out how I can run
a rule which will find out if a certain criteria are met in the data range. Each row has a value that is either in red, blue or black font, I want to flag these up in col AQ, if the rule is met. The rule is two in one so if col A contains red font the rule starts, it must there after NOT FIND BLUE FONT in any of the following columns B, D, F, H,I,L,N,Q,R,S,T,U,V,W,AE,AH,AI,AK,AL,AM. It then displays a RED (Y) in col AQ if no blue font is found. so if col A contains blue font the rule starts, it must there after NOT FIND RED FONT in any of the following columns B, D, F, H,I,L,N,Q,R,S,T,U,V,W,AE,AH,AI,AK,AL,AM. It then displays a BLUE (Y) in col AQ if no red font is found. I have to copy & paste the data into col A:AP and therefore the Y or symbol needs to be in col AQ. Hope that makes sense & any help is appreciated. |
Macro, formula help needed,, please
The Function below will return either "RED,"BLUE" or "". Call with
=ColorRule(A1:AM1) to test row 1 Note: Red and blue could have multiple values. # and 5 are the usual values but depending on the color palette you may have other values on your spreadsheet for red and blue. See VBA help PatternColorIndex Property Function ColorRule(target As Range) RED = 3 BLUE = 5 TestCols = Array("B", "D", "F", "H", "I", _ "L", "N", "Q", "R", "S", _ "T", "U", "V", "W", "AE", _ "AH", "AI", "AK", "AL", "AM") ColorRule = "" RowNum = target.Row Select Case Range("A" & RowNum).Font.ColorIndex Case RED: FoundBlue = False For Each Col In TestCols If Range(Col & RowNum).Font.ColorIndex = BLUE Then FoundBlue = True End If Next Col If FoundBlue = False Then ColorRule = "RED" End If Case BLUE: FoundRed = False For Each Col In TestCols If Range(Col & RowNum).Font.ColorIndex = RED Then FoundRed = True End If Next Col If FoundRed = False Then ColorRule = "Blue" End If End Select End Function "RunRonnyRun" wrote: I have no idea how to explain this but I am looking to find out how I can run a rule which will find out if a certain criteria are met in the data range. Each row has a value that is either in red, blue or black font, I want to flag these up in col AQ, if the rule is met. The rule is two in one so if col A contains red font the rule starts, it must there after NOT FIND BLUE FONT in any of the following columns B, D, F, H,I,L,N,Q,R,S,T,U,V,W,AE,AH,AI,AK,AL,AM. It then displays a RED (Y) in col AQ if no blue font is found. so if col A contains blue font the rule starts, it must there after NOT FIND RED FONT in any of the following columns B, D, F, H,I,L,N,Q,R,S,T,U,V,W,AE,AH,AI,AK,AL,AM. It then displays a BLUE (Y) in col AQ if no red font is found. I have to copy & paste the data into col A:AP and therefore the Y or symbol needs to be in col AQ. Hope that makes sense & any help is appreciated. |
All times are GMT +1. The time now is 10:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com