Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula and Macro Needed | Excel Programming | |||
Help needed with a formula or macro | Excel Discussion (Misc queries) | |||
Please Macro or Formula Help needed | Excel Programming | |||
Macro or Formula needed | Excel Discussion (Misc queries) | |||
Formula or Macro needed? | Excel Discussion (Misc queries) |