Free Microsoft Excel 2013 Quick Reference

Selecting Range of Cells based on Criteria

Hi,

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.

G

Related Results

Add a range of cells based on criteria

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.

Thanks.

Change fill color of a range of cells based on color of a cell?

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..

Change fill color of a range of cells based on color of a cell?

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..

Filling a range of cells based on a cell in another work sheet

Hi All,

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.

Formula for summing a range of cells based on cell color?

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.

Summing a range of cells based on lookup value

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?

Setting text values to a range of cells based on the choice in a combo box

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?

Clear contents of cells based on the values of other 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
Range("T4").Select
Selection.Clear
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
etc.
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?
Regards
mac

Formatting row of cells based on a cell content

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,
dcoates

How to lock and unlock a range of cells based on certain cell's value?

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")
.Unprotect

'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
Else
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.

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

Move Range Of Cells Based On Cell Criteria

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.

```
VB:
MoveRangeOfCellsBasedOnCellCriteria()
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
Else
cell.Range("A1:C1").Select
Selection.Cut
cell.Offset(0, -1).Range("A1").Select
ActiveSheet.Paste
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.....

Need code to select a range of cells based on criteria in column H

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!

Auto-(un)hide ranges of rows based on cell value

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.

Hide and unhide range of rows based on cell dropdown selection in protected sheet

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.

Copy/paste Averge of cells based on two matching criteria in cells

Hello,

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.

http://www.speedyshare.com/files/267...eDataSheet.xls

Cheers
photopidge

UDF will Avearge a range of cells based on their fill colour

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 http://www.ozgrid.com/VBA/Sum.htm, 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 ...

```
VB:
Range)

''''''''''''''''''''''''''''''''''''''
'www.ozgrid.com
'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

```

Get range of cells based on colour

Hi - have been trying to use code from
http://www.cpearson.com/excel/colors.htm 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
fine)?

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
End If
Else
If (Rng.Interior.ColorIndex = WhatColorIndex) = True Then
End If
End If
Next Rng

End Function

skellis

In excel change color of a range of cells based on a cell value

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

Locking a Range of Cells Based on Other Cell Values

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 [B6] < "2" Then
[B24]
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.

Formatting Range of Cells based on their values

Hi,

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.

How can I select a range of cells based on a value of a cell?

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.
Thanks,

Macro Problem, copy range of cells based on criteria

Hello,
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

B
C
A
B
C *
1
2 <====
3
D
E *
F
E
D
C +
1
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

C
1
2
3
D
E

and

C
1
2
E

Appreciate any help

Sum a range of cells based on user input

Hello,

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?

Macro to Change the colour of cell based on text

Hi all,

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

The selected range has values like -

1673030
1673020
1674020
1674030

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