conditional formatting by text colour

[whitepaw]

Help please - first time user and VBA virgin! I have an Excel 2007 spreadsheet containing several columns of data which has been conditionally formatted to turn text red when certain basic conditions are met. I now need to create a column to return a given value, say A or Z when either ANY of the cells in a certain range are red or say when there is a total absence of red text in that range of cells. I am really hopeful this is do-able.

I have attached an example of the spreadsheet. Columns M thru Y contain the conditionally formatted data (the abovementioned range)and column Z is the column that I am hoping to be able to automatically detect red text or the absence of and automatically return the A or Z that I have been manually recording so far. Any assistance would be greatly appreciated. Cheers

[Bob Phillips]

Your description doesn't match the spreadhseet, column N for instance is not CF but direct colour.

What you need to do is check the values in a formula and derive the A or Z from that.

[Simon Lloyd]

I've not looked at your workbook as i don't have 2007 here however this code may help you out, insert it in a standard module:[VBA]Sub Check_Red()
Dim Rng As Range, MyCell As Range
Application.ScreenUpdating = False
For i = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp) To 1 Step -1
For Each MyCell In Sheets("Sheet1").Range("M" & i & ":Y" & i)
If MyCell.Font.ColorIndex = 3 Then
Sheets("Sheet1").Range("Z" & i).Value = "A"
GoTo Nxt
Else
Sheets("Sheet1").Range("Z" & i).Value = "Z"
End If
Next MyCell
Nxt:
Next i
Application.ScreenUpdating = True
End Sub
[/VBA]

[whitepaw]

Thanks both for your help. XLD, yes sorry I forgot that columns N & Y have red as direct colour and black is CF colour. Please delete both column if it assists. I need to leean to do this by VBA as this is a only a basic example of my spreadsheet. Simon, thanks for your help - I will buy a book today on VBA to try to learn how to write this code. cheers

[Bob Phillips]

You don't need VBA.

Just add a formula of

=IF(OR(AND($N1="sl",$Y1="sl"),AND(P2>=8,P2<=14),Q2>=2,R2>8),"A","Z")

This is just for the first few columns, you can do the rest.

[Simon Lloyd]

You don't need VBA.

Just add a formula of

=IF(OR(AND($N1="sl",$Y1="sl"),AND(P2>=8,P2<=14),Q2>=2,R2>8),"A","Z")

This is just for the first few columns, you can do the rest.

Bob, nice to see you being true to worksheet functions!

As i said i only supplied the code because i couldn't see the workbook, i didn't really grasp what the OP wanted as he didn't say which should be A which should be Z and what should happen should the colour be xlautomatic or the cell blank, so in essence the code is useless without knowing all the parameters that should be catered for!

[Bob Phillips]

Bob, nice to see you being true to worksheet functions!

As i said i only supplied the code because i couldn't see the workbook, i didn't really grasp what the OP wanted as he didn't say which should be A which should be Z and what should happen should the colour be xlautomatic or the cell blank, so in essence the code is useless without knowing all the parameters that should be catered for!

My comment was aoimed at the OP Simon, he needs to master Excel a bit more b efore he migrtaes up to VBA.

[whitepaw]

Thank you both for your replys. I posed my question on a simlar excel forum and was advised to seek a solution via VBA. As my spreadsheet contains several columns each with several variants, I considered that one VBA code selecting conditional formatted outcomes was preferable to an excel formula that would need to cover up to a hundred or more possible scenarios. Unless what I am trying to acheive (selection of cells based on font colour) is not possible on VBA, I am determined to find a way to acheive this as I consider it the most practical solution to my current problem and for future applications. I do accept that I need to learn more on excel but every expert was once a learner like me. I do though sincerely thank you both for your time and efforts to help me with this. Regards, Whitepaw.

[Simon Lloyd]

Whitepaw, it probably is achievable with VBA but without understanding the rudimentry functions of the worksheet you will have difficulty relating it to VBA let alone understand it, you would be better of trying to simplify and unify the structure and layout of your worksheets. The wise xld told me many years back "Plan now save on pain later", it's best to work your structure and layout on paper first before diving straight in, when you know what it is you will have, what you would like to see and what events should happen in order to achieve the final look you will be half way there.

As a side note, it is customary and courtesy to post the link(s) to your posts in other forums, for an explanation why click the link in my signature.