Excel consecutive formula

[mahmed1]

Hiya

I have a row of data in cells D3, E3, F3 etc

what i want to do is highlight the values in there to see if the consecutive values are greater than 3 and then highlight it

ie if its 1, 1, 3, 4, 5, 5 (highlighted), 3, 2, 5,5,5 (Highlighted), 5 (Highlighted), 5 (Highlighted), 2,4,4,4 (Highlighted)

so im just trying to highlight all values that exceeda number 3 in my case 3 times in a row , if it dropa back then the count should find the next occurence and highlight until it drops back again

hoping im making sense of it

Im typing from a phone hence why i could highlight the values so have put highligh next to the value

 

[mahmed1]

Sorry i just seen your latest response

Im using a proper excel 2016 and also have office365 which is what il be mainly using

I can have VBA as an option if thats the better solution as that way i can apply the formatting to in
F33: Last column 33 and check to see if the formula in 34 is consecutive to cell C33

Therre is multiple rows that have different variables ie

F35:last column 35 - variable to check is in C35 etc

 

[mahmed1]

I guess what im trying to say is with VBA i can apply in multiple rows and check against the variable in column C in that row for consecutive occurance (there is a formula in cells below just to put 1 or 0 if the condition matches) that’s basically just checking to see if the value is greater than the variable in column B In the same row and if it is then Put a 1

 

[mahmed1]

Hi Rick

Is there any chance you could help me with a Vba solution that will allow me to change the variable days? as each row could have different consecutive days to compare with

Thank you so much and appreciate your help

 

[mahmed1]

Hi Rick

Do you need any more info
Sorry if ive not been as clear

 

[Rick Rothstein]

Hi Rick i tried that but unfortunately it was not highlighting the right cells

in the example below - it should be highlighted like this

View attachment 23886

Can you clarify the above from Message #4 for me? Are the only values in your cells 0 or 1? If so, did you only want to highlight consecutive 1 values and not consecutive 0 values?

 

[mahmed1]

Hiya thats right

So in the cells below (say my values are in row 3) i have a formula in row 4 to say if the value above is greater than the condition percentage value then give 1 else return 0 (So yep there is either 1 or 0)

I then want to only highlight the values in row 3 only if it the 1 value is consecutive to whatever the day variable is

So eg if my day variable was 4 then
Only highlight the 1 value that appears 4 time consecutively if it appears 5 times consecutively then highlight that too

0,1,1,0,0,1,1,1,1(highlight), 1 (highlight), 0,0,1,1,1,1(highlight)

If the day condition is 2 then
1,0,0,1,1 (highlight), 1 (highlight),0,0,0,1,1 (highlight),0,0,0,1,1 (highlight), 1 (highlight) etc...

Hope this makes sense (the day condition will be column C in the same row
Eg if the values to highlight is in row 3 Then the day variable will be in row C3

If in row 7 then day variable will be in row C7
The data starts from column F and can continue to as far right

I guess i can fire the code if something in column c changes and then what ever cell in row C changed then examine everything in that row (this way it avoids) hard coding every row i need examining and it will only examine that row but im easy even if its not triggered by an event and i can hard code all the rows i need

For now values to examine are in row 3, 7, 11, 15 etc and the condition with the 1s and 0 is in the row below

 

[Rick Rothstein]

For now values to examine are in row 3, 7, 11, 15 etc and the condition with the 1s and 0 is in the row below

What do you mean "for now"? How might they change (might affect how I have to write the code)?

 

[mahmed1]

sorry ignore what i said re for now

What i meant is if i added more rows ie at the moment its 3, 7,11,17 and more could be added

 

[mahmed1]

I hope i was clear in my previous post - please advise if you require any more info

 

[mahmed1]

@Rick Rothstein Hi Rick - is there any chance you can be kind enough to help me

Apologies if ive still not been clear im my requirement