Excel consecutive formula

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Rick

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,334
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,334
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I hope i was clear in my previous post - please advise if you require any more info
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
@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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,413
Messages
5,528,625
Members
409,828
Latest member
99DodgeRam

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top