I want a macro that will look for 5 consecutive cells in a row that all have the values I want in them.

If they have the values I’d like then I would like to highlight the row and add some text to the next cell (the 6th cell).

This is what i mean:
If (A1, B1, C1, D1, E1) == 1 0 1 0 1
Then set F1 = "some text value"

This would be the result.

   A B C D E F
1  1 0 1 0 1 Some text value

5/18/2006 4:07:45 PM

You can just do this with a regular formula dork.

5/18/2006 4:09:07 PM

Quote :

"You can just do this with a regular formula dork."

Thanks, You're oh-so helpful.

Edit:
I don't know much about excel so I could be wrong, but I didn't think a formula would be the best way to do it when I’m really looking for multiple sequences, and I want each sequence to get a different label in cell number six.

Basically I'm trying to build an inverse assembler with a bunch of crap that someone sent me in an excel file.

The example I gave is an over simplified example. I was planning on taking the code and adapting it to exactly what I need. I actually have 7 cells in a row and sometimes the last two are "don't cares."

[Edited on May 18, 2006 at 4:17 PM. Reason : .]

5/18/2006 4:10:59 PM

try something like

=if(and(a1=1,b1=0,c1=1,d1=0,e1=1),"match","no match")

and conditional formatting to highlight

5/18/2006 4:24:59 PM

How many different sequences are we talking about here?

5/18/2006 4:26:20 PM

^Ok cool, the matching works, and I think I can expand it to work on multiple sequences.

Still one problem though, Conditional formatting only allows for 3 conditions and I would like to be able to highlight 4-5 different states.

5/18/2006 4:32:24 PM

Quote :

"How many different sequences are we talking about here?"

17

edit:
http://www.infineon.com/upload/Document/HYB18H512321AF_Rev1.73_2005-08-18.pdf
page: 16 for all sequences.

[Edited on May 18, 2006 at 4:37 PM. Reason : .]

5/18/2006 4:32:46 PM

if you need more than 3 conditions, i think you will probably have to do a macro. google "excel macro highlight row" and you should find several examples of how to write one

5/18/2006 4:35:28 PM

one trick you can do with conditional formatting to make it look like you have 4 conditions instead of the 3 that you can put in with the COnditional Formatting box is to set the default format for the cell something other than "normal". e.g. if you want 4 different colored cells, you can set all the cells to default to a certain color, then set 3 conditional formats to make them something else. Of course, if you want a certain percentage of cells to remain unhighlighted somehow, this doesn't work as well.

if you want to write a macro, the best way is probably to make a Function that will compare the 5 cells, set the formatting, then return the value to display in the adjacent cell. If you create a VBA Function (called "Compare5", for example, you can call that function direcly in a cell by "=Compare5(range)", where 'range' is the range of 5 cells. YOu can also put in the desired values into the argument, or you can hardcode them into the Funciton.

i'd have to look at my Macro examples at work to give you exact syntax, because i can't really write VBA without examples, but the function would look very roughly something like this:

Public Function Compare5(CompareValues As Range) Return String 
  If (RC[1]=1 And RC[2]=0 And RC[3]=1 And RC[4]=0 And RC[5]=1) Then
    Compare5 = "Match"
    If (condition1) Then
      Range(RC).Color = ColorIndex.5
    ElseIf (condition2) Then
      Range(RC).Color = ColorIndex.3
    ElseIf (etc etc)
      more conditions....
    EndIf
  Else
    Compare5 = "No Match"
  End If
End Function

5/18/2006 4:54:10 PM

ok, after reading over all this again i have some different suggestions.

It's apparent that you have a DRAM command sequence written only in terms of CKE, CS, RAS, CAS and WE and you want to convert that into an actual command sequence with RD, WR, ACT, etc. So do this:
In column F, concatenate the 5 1-bit commands into a single string.

=A1&B1&C1&D1&E1

10101   Read
10100   Write
10010   Precharge

=VLOOKUP(F1,$I$1:$J$17,2,FALSE)

5/19/2006 8:27:41 AM

Thanks for all your input agentlion.

I've got it working now.

5/19/2006 9:21:18 AM

post it.

5/19/2006 12:43:30 PM