Need help hi lighting numbers with formulas in a cell

[frogman]

I have formulas in B2:E51 on this sheet and other sheets from B2:I51 and other sheets B2:E3001 and B2:I3001.

I want to be able to Place only the numbers that get hi lighted and place the results over in say column AK BUT I need them in the lowest numeric value if possible, meaning a 12 is a 3 because 12=1+2=3, 27 would be a 9 because 2+7=9. Is this even possible with a formula or macro?

16801

[jolivanes]

How are the cells hi lighted?
If it is through Conditional Formatting, you cant use the color for separation purpose

[SamT]

I just uploaded an attachment with subs dealing with cell colors and formulas
See the last post in
Set Range to Existing Range Name based on criteria (http://www.vbaexpress.com/forum/showthread.php?56789-Set-Range-to-Existing-Range-Name-based-on-criteria)

To add the digits in a number:

strNumber = Cell.Text
Do
For i = 1 to Len(strNumber)
Result = Result + CInt(Mid(StrNumber, i, 1))
Next
If Len(Result) > 1 Then strNumber = CStr(Result) 'if number 19, 28, etc, result >= 10
Loop While Len(strNumber) > 1

[jolivanes]

A more long winded approach assuming that the yellow colors are the result of CF and cannot be used.
Works on the hidden columns G to J down to the last used cell (of column A).

Sub Try()
Dim lr As Long, i As Long, c As Range, s As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lr
For Each c In Range(Cells(i, 7), Cells(i, 10))
If c.Value = 1 Then s = c.Offset(, -5).Value: Exit For
Next c
Select Case Len(CStr(s))
Case Is = 1
Cells(i, 37).Value = s * 1
Case Is = 2
Cells(i, 37).Value = Left(s, 1) * 1 + Right(s, 1) * 1
Case Else
End Select
Next i
Application.ScreenUpdating = True
End Sub

[frogman]

The yellow is produced in CF by a formula like =G2=1. Whenever a 1 is placed in G1:J51 that cell gets high lighted yellow.

i was hoping to find a way to "only" capture the cell with its number in it and place it n a separate cell for tracking purposes. Is there not a macro that can say "when dell turns yellow place number in cell x? Just reaching y'all are the experts

[jolivanes]

Re: when dell turns yellow place number in cell x?
No you can't. However, you can use the offset again as in previous (#4) post.
Something in this style: If cell has a value of 1 then the value in cell AF2 = the value of cell B2

[frogman]

"X" means it can be in any cell. Sry for any confusion. I'll go back and try what's in post 4

[frogman]

POST 4

My apologies,this works PERFECTLY!!!! THANK YOU!!!!

[mikerickson]

Since the coloring comes from Conditional Formatting based on columns G:J.

If you put =MOD(SUMPRODUCT($B2:$E2,$G2:$J2),9) in AK2 and drag down, you should get the results that you want.


(142 Format Conditions is probably too many.)

[frogman]

@jolivanes (http://www.vbaexpress.com/forum/member.php?1993-jolivanes)



If the yellow is in columns CEGI instead of BCDE on other sheets how can I change this Macro to accommodate those?

[mikerickson]

Since the color comes from conditional formatting reading cells in G:J, working with those cells is easier than trying to read CF colors.
See my post above.

[frogman]

@mikerickson

That works GREAT but how would I do the same for these that are on the other sheets? Thank you!!!

16813

[mikerickson]

Just adjust the ranges. In my formula, the first term of the Sumproduct is the range with the values, B2:E2 and the second, G2:J2, is the range that is the source of the CF coloring. If you have similar ranges elsewhere, just change the formula to match the other cell addresses.

[frogman]

The only issue with this is a 9 registers as a 0 in AK

=MOD(SUMPRODUCT($B2:$E2,$G2:$J2),9)

[frogman]

Just adjust the ranges. In my formula, the first term of the Sumproduct is the range with the values, B2:E2 and the second, G2:J2, is the range that is the source of the CF coloring. If you have similar ranges elsewhere, just change the formula to match the other cell addresses.

When I tried this =MOD(SUMPRODUCT($C2:$C2,$E2:$E2,$G2:$G2,$I2:$I2),9) it does not work so I m confused. so sorry

If I do this =MOD(SUMPRODUCT($C2:$E2,$G2:$J2),9) I get a #VALUE error


I want to skip BDFH and not use those

[mikerickson]

I don't know what cells are used to control the CF for which other cells in your new lay-out.

[frogman]

Ok folks this is what I'm doing to make it easier for all of us. I've moved what was in columns CEGI and delete those columns so the Macro works in post 4 for all of the sheets and there are 75 sheets.

@Mikericson If there is a way to fix the formula That when a 9 is present it registers a 9 instead of a 0 that would be great as it would speed things up

THANK YOU ALL

[jolivanes]

Consistency will do the trick.
Good luck