Free Microsoft Excel 2013 Quick Reference

Selecting Range of Cells based on Criteria


New member here.

I'm looking for a solution for to be able to select a range of cells based on the yes/no value in another cell in the same row.

Cells A12, A13, A14, etc will either have a yes/no value. Based on the cell having a yes value, I'd like to select cells B12:M12, B13:M13, etc.

I have been able to workout how to do the select but not based on criteria.

Any help or direction would be greatly appreciated.


Post your answer or comment

comments powered by Disqus
I know the basics on excel formulas. I am trying to add a range of formulas based on criteria on one of the columns.

If x cell's value is RCNC then count the next 12 cells on the same row as RCNC = subtotal

Then I need to add all the subtotals of multiple rows to give me a Grand total.

Hopefully somebody can help me with this formula that is too complicated for my basic knowledge of excel.


I want to change the color of a range of cells based on the color of a
different cell. How do I do this. I tried conditional formatting but this
only lets me specify the color in the conditional formatting box. It does
not allow me to point that to a cell and a color reference.

For example - cell A1 is red - change cells A5-A8 to red. Cell B1 is green
- change cells B2-B11 green etc..

I want to change the color of a range of cells based on the color of a
different cell. How do I do this. I tried conditional formatting but this
only lets me specify the color in the conditional formatting box. It does
not allow me to point that to a cell and a color reference.

For example - cell A1 is red - change cells A5-A8 to red. Cell B1 is green
- change cells B2-B11 green etc..

Hi All,

I am new so please forgive me if this has already been answered.
I am trying to fill a range of cells based on an input in a cell in another work sheet. Is this possible, I do not want to put a formulas in the destination cells because the range to be filled will change and I also do not want to lose the formula should some have to change the destination cells.

Thank you in advance

I am looking for a formula that will sum a range of cells based on a certain color. I have conditional formated certain rows, and i would like a formula that sums the rows in the sheet looking for that color.

Hi Folks,

I've been trying to figure this out for the past few days but with no luck.

I want to sum a range of values based on whether a cell has been marked with an x and based on the priority of the column the cell is in.

Each priority has a weighting assigned to it in a table: High = 100, Medium = 50, Low = 10

If I change the priority the weighting will change accordingly.

So I want to lookup the value of the priority and check if the cell in the column has been marked with an x and I want to do this fol all columns in the selected range. I then want to sum all the priority values

The user will fill in x's where they want, they may remove x's also

I want to be able to sum the total weighting within a specified range where the cells have an x in them.

I have attached a workbook to show what I'm trying to do..

Is this possible?

Thanks in advance..

I am new to Excel VBA although I've done some Access coding.
I need to set the text values of several cells based on another cell who's value is set by a combo box selection (updates a cell based on the selection).
I would like to use a Select Case to keep things tidy.
What are the Events that will drive the updates and How do I reference the cells?

Hi there

I am looking for some inspiration to help with an issue I have. I want to be able to clear the content of a range of cells based on the values of other cells. However, I want to do this individually.

As an example I have cell E4 and T4. I want to clear the contents of T4 as soon as a value is entered in E4.

I used the following coding to achieve this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Value As Variant

Value = Range("E4").Value
If (Value <> LastE4Value) Then
LastE4Value = Value
End If
End Sub
This is okay to clear an individual cell. However, I also want it to do the same for a range of cells so that whenever I enter data into any one of the range it clears the contents of the corresponding cells i.e.
T4 to clear when data entered in E4
T5 to clear when data entered in E5
T6 to clear when data entered in E6
The actual range is E4:P53 with the corresponding cells to clear as T4:AE53
Can anyone suggest a way to achieve this without using the above code 600 times to cover the cell range required?

I want to format a range of cells based on the name listed in a cell in column A (starting @A6). I currently do this manually. It is to help me visually see the line I am working with and for what person.

I am almost certain that is going to take a VBA, but I know little to nothing about setting one up.

I have attached a sample. I am using Excel 2003

Thanks as always to the forum for the wonderful help,

Hi everyone,

I am writing a macro to protect/lock a range of cells that are irrelevant to the user. However, I am very confused over the "protect" property and the "locked" property. Could some kind soul out there help to explain the differences to me??

Also, I wanted to write a macro to prevent users from entering anything inside a specific range of cells, depending on the different stage the users are currently in. I thought of using the password protection function in excel to do it but i am not sure how to achieve that using codes.

I have a specific cell to let users to indicate which stage they are in, so that I could depend on the value in the cell to lock those irrelevant range of cells (I don't want the users to enter anything for irrelevant fields) and unlock those relevant range of cells for users to enter inputs. Before I could use the specific cell to validate, I will need to lock all the fields before users have indicated which stage they are currently in. However, I do not know how to code it in a way that will achieve the results.

The following is something that I tried to come out with after researching online but not sure is it the correct way.
Sub test1()
With Application.ActiveWorkbook.Worksheets("sheet 1")

'This is to check what to lock/unlock depending on cell("C7")
If .Range("C7").Value = "stage 1" Then
    .Range("H11:I37").Locked = True
ElseIf .Range("C7").Value = "stage 2" Then
    .Range("H12:I37").Locked = True
ElseIf .Range("C7").Value = "stage 3" Then
    .Range("H17:I37").Locked = True
ElseIf .Range("C7").Value = "stage 4" Then
    .Range("H23:I37").Locked = True
ElseIf .Range("C7").Value = "stage 5" Then
    .Range("H33:I37").Locked = True
ElseIf .Range("C7").Value = "stage 6" Then
    .Range("H35:I37").Locked = True
ElseIf .Range("C7").Value = "stage 7" Then
    'to unlock all the cells for users to input
    .Range("H10:I37").Locked = False
    MsgBox ("Please indicate your current stage")
End If

.Protect Contents:=True, userinterfaceonly:=True
End With
End Sub
I have tried out the codes and it works fine. However, what if I want this piece of code to be triggered when the users first entered something on the worksheet? Not sure how to code it.

Please help! Thanks a million in advance.

Here is the sample file without the coding (explanation included):

I've written the following bit of code but I know it's not efficient. ie I know I shouldn't be using the select command.

Basically, what I'm trying to do is loop through a range of cells, and if the cell meets a condition I want to move it and 2 cells to the right of it left by one cell.

    Dim myrange, cell As Range 
    Set myrange = ActiveSheet.Range("H2", Range("H65536").End(xlUp)) 
    For Each cell In myrange 
        If IsNumeric(Left(cell.Value, 1)) _ 
        Or Left(cell.Value, 5) = "UNIT " _ 
        Or Left(cell.Value, 4) = "THE " _ 
        Or Left(cell.Value, 5) = "FLAT " Then 
            cell.Offset(0, -1).Range("A1").Select 
            cell.Offset(1, 1).Range("A1").Select 
        End If 
    Next cell 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help appreciated on this cold Friday morning.....

So here is what I need to do: I have a range of data in columns A through H. Column H has only 0's and 1's. It is sorted 0's on top and 1's underneath (ascending). The number of rows with 0's or 1's changes daily as i run my first code which pulls in fresh data.

I need to get code that will select A1 as the start of the range, and find the last 0 in column H as the end of the range. Help!

I'm a VBA newbie and I'm trying to find out how to hide a range of rows based on multiple conditions. I've looked through several threads and I haven't had much luck. Any help would be appreciated. Here's what I'm trying to do:

If G10 = "Summary Charts", then hide rows 6:9
If G10 = "Selection B", then hide row 9
If G10 = "Selection C", then hide rows 8:9

I have about 7 conditions similar to what is described above.

I want this to be dynamic, since it is based on the selection made within a drop down menu.

Hi, Thanks to anyone willing to help. I am attempting to hide a range of rows based on a dropdown box value in E91. If E91 = "Itemized", I want rows 119-127 unhidden. Else, I want them hidden. I would like the sheet to run the macro only when that particular item is changed so it does not calculate all the time. Also this is going onto a password protected sheet. I have spent 3 days searching through forums and trying to paste together code to make it work with frustrating results. Any help would be greatly appreciated. Thanks in advance. If this specific situation has been answered in the past, I apologize. I simply have not yet found it.


This forum has been extremely helpful. I am hoping someone can help me. I would like to take the average of cells based on to criteria. In my sample data set, there is a number for fish, and then a column of colour. For one fish, I need it to average the numbers for the same colour. I figure this would use two if/then statements, but I am unsure how to average based on two criteria.

As an example, for the same fishID (e.g. G100858), I would like an average of the UVSens for the same colour. For this particular fish, there are 4 orange spots that I would like to average UVSens for. Then the 2 Bronze, and then the 2 Violets. I would like averages for Columns D through H pasted into a new sheet with fishId, Colour and then the average of UVSens, SWSens, MWSens and LWSens.

The datasheet is uploaded at the following link.

Thank you in advance

I am not a whiz at macros and I can sometimes figure them out, but this one has really got me stumped. I love the function that is explained at, but I am trying to figure out how to make it do Average instead of Sum. Any help will be greatly appreciated.

The function on that ozgrid page is ...

     'Written by Ozgrid Business Applications
     'Sums cells based on a specified fill color.
    Dim rCell As Range 
    Dim iCol As Integer 
    Dim vResult 
    iCol = rColor.Interior.ColorIndex 
    For Each rCell In rSumRange 
        If rCell.Interior.ColorIndex = iCol Then 
            vResult = WorksheetFunction.Sum(rCell) + vResult 
        End If 
    Next rCell 
    SumColor = vResult 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Hi - have been trying to use code from which enables me to return a
cell range based on background colour of cells. However, the code gives
me an "Error: Sub or function not defined" message. Can someone tell me
why (other functions for counting cells based on colour work just

Code is:

Function RangeOfColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Range
' This function returns a Range of cells in InRange with a
' background color, or if OfText is True a font color,
' equal to WhatColorIndex.
Dim Rng As Range
Application.Volatile True

For Each Rng In InRange.Cells
If OfText = True Then
If (Rng.Font.ColorIndex = WhatColorIndex) = True Then
Set RangeOfColor = AddRange(RangeOfColor, Rng)
End If
If (Rng.Interior.ColorIndex = WhatColorIndex) = True Then
Set RangeOfColor = AddRange(RangeOfColor, Rng)
End If
End If
Next Rng

End Function

Thanks in advance.

Based on a cell value from a drop down box, how can you make a range of cells
to change background color?

I have a worksheet that I want to have different ranges of cells locked, based on a different cells value. What I have right now is something that just locks an individual cell. I can't seem to figure out how to adjust the code to make it work for a range. It is probably very easy, but I cant figure it out. I have a few other ranges that I would want to apply this to, instead of adding the current code I have to each individual cell. But if I could have someone adjust my code for this range, I could adjust it for the others.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Address <> Target.Address Then Exit Sub
If Target.Address = "$B$24" Then
If [B6] < "2" Then
MsgBox "Invalid amount of" & vbCrLf & _
"outpatient visits", 64, "Access into cell not allowed."
End If
End If
End Sub

The cell that it should be referencing is B6 and the range of cells I want the code to apply to is A24:B27.

Thanks in advance!!


Plz look at the attached file.

Now i am trying to change the Font color of a cell (F10), based on its cell value.

"Complete" = green
"Incomplete" = Red
"NA"= grey

the cell value is based on its customized formula like
F10 =IF(COUNTIF(F36:F37,"NA")>(ROW(F37)-ROW(F36)),"NA",IF(COUNTIF(F36:F37,"Incomplete")>0,"Incomplete","Complete"))

and i wanted to change the font color for a range of cells. every cell will have either of the states (Complete,Incomplete or NA) based on its customized formula

Now the problem is

when i apply this formuala, all the range of cells are changing to only one colour (Either RED,GREEN or GREY) though they have different values inside them.

How can i fix this?

Hope you understand the problem.

i am new to excell, so can you please explain me in clear steps on how to solve this.

Thank You.

I import an excel table from another application.I want to make a macro to:
1.sort table -it'easy
2.from sorted table to select a range of cells until a value from sorted
table is matched.This is the problem.
3.copy selected range in another sheet-it's easy.

This is my first post so I am hoping I can get some help.

I need to write a macro that will search through a huge dataset in column A (may be 65000 rows) and for each time a certain value appears search up to another value and down to another value and copy that range.

Example: Assuming the below is column A

C *
2 <====
E *
C +
2 <====
E +

For each time "2" appears i need it to search for the nearest "C" above it (Marked with a *in first section and a + in second section) and the nearest "E" below it (also * and +) and copy that range to a new column. all values will repeat multiple times throughout the sheet so it has to copy the "E" and "C" nearest to the "2". There is also a varying number of rows between "C" and "E".

So based on the example above the answers I would like to get in column B are




Appreciate any help


I have a range of cells that I want to sum based on a range inputed by the user. The range begins with cell c27 and ends with cell au27. I want the user to be asked the beginning cell and ending cell of the range they want to sum and then output the answer. Any suggestions?

Thanks in advance

Hi all,

I want a macro which can change the colour of cell based on text.

The selected range has values like -


In the selected range if the fourth charactor is "4" then cell should be coloured in blue, else in red.

Can anybody help me please.

No luck finding an answer? You could always try Google.