Macro, formula help needed,, please


R

RunRonnyRun

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.
 
Ad

Advertisements

J

Joel

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top