Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Macro that will clear contents of cell based on format of text in adjacent cell

Hi All,
Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail.

I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.

Any ideas?

Thanks in advance for the help.

Cheers,
Tim


Post your answer or comment

comments powered by Disqus
Creating macro that inserts data into a cell based on certain critera in spreadsheet

let's say that I have a range of data in column A and based on that data I am inserting certain values in column b. For example, if cell a1, contains phrases like "£25" and/or "music" I will put "concert" in cell b1. Additionally, if cell a1 doesn't contain those phrases but contains other ones such as "£10" and/or "book", I will put "study" in cell b1. I basically want a way of automating this in a macro, please assume that I have little or no experience of creating macros.

Many thanks in advance

Hi everybody,

I am a financial administrator & every month I have down load 4 bank accts as CSV, import

into xl & code (CACode) the amounts for our accountant.

I have VBA that formats, adds headings & formula etc but I have a problem/s.

In H col I place CAcode & I use a sumif formula in I col to sum all the amounts with that have the same CAcode. For simplicity sake I copy the sumif down & then sort H col ascendindingly (this is done by VBA).

Now I am trying write a macro to clear the contents of the cell in I col
if eg h60 = h59
then I60 clearContents, Select h59
Else select H59
Do until H3 is selected

Psuedocode
Select table (A2:I Xldown)
Sort Table by CAcode (H Col)
Set Range as H3:Xldown
Select Last cell with CAcode (Xldown) in H col
For every cell in Range (H3:Xldown)
Use If/then
If Last cell = 2nd Last cell (H Col) then
Clear contents of I col (last row)
select 2nd Last cell (H Col)
Else 2nd Last cell (H Col)Next Cell

I have to use Xldown to select range as the range will be variable each month & for each bank acct.

I need to clear contents of cell to verify that all the sub-totals of unique CAcodes of the CAcoded amounts = the totals

Because I may have up to 120 rows X 3 bank accts I am slowly using vba to do my work.


	VB:
	
 sort_And_delete_Sumif_amounts() 
     '
     ' sort_And_delete_Sumif_amounts Macro
     ' Macro recorded 6/03/2007 by Lionel Hansen
     'This deletes sumif formula in I Col if
     'Last cell CAcode = 2nd last cell CAcode in H Col
     
     '
    Dim r As Range 
     'Select range to sort
    Range("A2:I98").Select 
     'Sort CAcode in H col ascendingly
    Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _ 
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
     'select range for comparisomn of CAcode
    Set r = Range("H3", Selection.End(xlDown)) 
     'Select last cell in CAcode range to start If/Then
    Range("h3").Selection.End(xlDown).Select.Range ("A1") 
    For Each Cell In r 
         'Compare CAcode in H Col
        If Range("A1").Value = ActiveCell.Offset(-1, 0).Value Then 
            ActiveCell.Offset(0, -1).Range("A1").ClearContents 
            ActiveCell.Offset(-1, 1).Range("A1").Select 
        Else: ActiveCell.Offset(-1, 0).Range ("A1") 
        End If 
    Next Cell 
     
     
End Sub 

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

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

Hi All

I have written some code that searches through a sheet and based on whether a cell is empty it returns an amount in another column. When I paste the worksheet into my current workbook there are cells that seem to be empty(Without clicking on them), but in reality if you click on them the bar at the top has ="" . So it's not showing the "" on the sheet but it does have something in it. The format is General. What I need to do is search through these 3 columns for this ="" and clear the contents because I'm using ISEMPTY(Activecell) through an IF statement. I have tried doing a paste special, this takes care of the ="" but then the ISEMPTY(activecell) is not giving me the correct thing, it still views the cell as populated. Can anyone help? If I need to upload some code, let me know.

OSU Guy

Hi, i need a macro written to clear contents of cells with criteria.

I have data starting in row A3, across 62 columns, this may vary depending on which month we are in. For an example i have chosen the month of May which has 31 days.

In Col A3 onwards is a list of p’folios and in Col B3 onwards is values associated with each p’folio. Col A and B represent 01-May, Col C and D represent 02-May etc
I need a macro to clear cells which have portfolios starting with “ZI” and “FI”, I don’t want the rows deleted but want the rows to move up one cell when data is cleared, I need this for every column of data.

So let’s look at Col A for an example, we can see it has p’folios ZISZAE and FISAAE, therefore will clear cells A3 and B3 move A4 and B4 one cell up, and the same scenario for A5 and B6 clear the contents and move Cells A6 and B6 up. The same can be repeated for all other columns,

Col A Col B Col C
01-May-08 Amount 02-May-08 Amount
3 ZISZAE 12 AIMTES 76.98
4 OPTMOD 15.8 ZIZZAE 89
5 FISAAE 344.87 CAETRN 76

Hi.

I already have a macro that clears the contents of a range of cells in a given row if a certain cell in that row is blank. I need to expand this but don't know how. Here is what needs to happen:

Beginning at row 5, I need to clear the contents of cells in columns AJI:AMT IF cells in columns WA, AOT, ASQ, AYI, BFB, and BPN are all blank as well. This would extend down to row 500.

The converse is that if AOT is blank but WA, ASQ, AYI, BFB, or BPN have a "No" in them, then the range AJI:AMT does not get cleared.

Thanks so much for the help!

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.

Can anybody help me please.

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.

The datasheet is uploaded at the following link.
http://www.speedyshare.com/files/267...eDataSheet.xls

Thank you in advance
Cheers
photopidge

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

Is there any way to use 'Fill' over a certain number of cells?
I mean, instead of selecting for instance, 21 cells and doing ctrl+d, could I just select the cell and get excel to fill 21 cells?
Even better, is there a way of filling a number of cells based on a value in a cell? For instance, if 15 is written in one cell, could excel be made to fill text over 15 cells?

Thanks,

Harvey

I am trying to change the color in a cell based on the text I enter in
another cell. EX. If I enter Arizona in cell B2 than cell B24 will turn red.
I need to do this for several answers, so B24 would need to change red if I
enter Arizona or Hawaii or Mississippi....ect.

Can someone help me with a macro that will clear contents in a range based on a cell?

im trying to get it so in range E30:E49, going down the range, the first empty cell will cause that entire row from A:L to be selected and contents cleared.
i want it to stop @ row 50, as there is data that needs to be kept here.

is this do-able?

I am looking to write a macro that will select a certain cell, the address of which is based in another cell. The macro is to form part of a database amendment form. Basically I have created a tab that will select an entry from a database tab (using a primary key), copies this data onto the edit tab so that it can be changed. I am now trying to write a macro that will copy the amended data over the top of the existing record.

I have a cell on the database sheet that uses a formula to display the address of the record that need over typing this is not the problem. All I am missing is the part of the macro that reads the cell A1 for example, then goes to the address dispalyed in that cell. I have the following code but it keeps bringing back a runtime error.

The tab the cell containin the address is in is called Sheet1 and the cell is A1


	VB:
	
 
Sub goToCellAddressInFormula() 
     
    Dim goToRange As Range 
     
    Set goToRange = Range(CStr(Sheet1.Range("A1").Value)) 
    Application.Goto Reference:=goToRange 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This code seem to work on a test workbook I created but then not on the original workbook, I do not know why. I am also having to save the file as 97-2003 document if this makes any difference to any answer.

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?

I have 5 columns in my spreadsheet.

All of the cells in Column D can be found in Column A, but not the other way around. For every occurrence in Column D, I want to find the row that corresponds in Column A (by identifying the same value), and then copy the value of that row in Column B - into Column E. I want to run this procedure entirely through Column D, until Column E is as long as Column D, and has the info I need.

I started writing a macro, but my programming is more than rusty, and I am not familiar with all of the syntax for cells and whatnot. I am not even sure if it would work, if I did know the syntax.

This is what I have... I have a feeling that if it actually ran, it might even end up looping forever. I dont know. I won't be offended or surprised if what i have is all wrong and there are more efficient ways of doing this.

Any help is much appreciated. Thanks!


	VB:
	
 makesTitles() 
    Dim original As Range 
    Dim errored As Range 
    Application.ScreenUpdating = False 
    For Each errored In Range("d2:d23405").SpecialCells(xlCellTypeConstants) 
        For Each original In Range("a2:a32782").SpecialCells(xlCellTypeConstants) 
            If errored.Value = original.Value Then 
                errored.Row = ErrorRow // dont knoiw If this Is necessary??? 
                original.Row = OriginalRow //same here?? 
                 
                ** this big blank Is me Not knowing how To proceed** 
                ** I thought that If I could get the OriginalRow To be used As part of a cell coordinate, I could Then "copy"
that cell, And "paste" that value To another cell based on the ErrorRow And column D 
                 
                **Column A Is "Original" And Column D Is "Error." 
                 
            End If 
        Next original 
    End If 
Next errored 
Application.ScreenUpdating = True 
End Sub 

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


I'm currently using a useful COUNTBOLD macro I've found elsewhere to count the total number of bold values in a column.

I now need a macro that will conditionally count bold cells based on the value in another column, in this instance whether data is male or female (M/F).

Any ideas?

Hi to all,
I'm not too good with macros, but i'm learning.
I am constructing a survey tool and would like a macro that automatically
goes to a cell based on the Yes or No response selected from a drop-down box
in another cell. For example, if NO then go to question #4.

Any thoughts? Much appreciated.

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

I need to make macro that will select only those cells that have no
values in it. How do i do that?

For example: lets say the excel sheet has values in B4 and A9 and A22.
How would i make a macro that will select all other cells except those
three cells above.?

Regrds

Hi Guys,

I'm hoping theres a fairly simple solution to this one, I'm pretty new to VB though have used excel for a long time but I can't get my head around how to do this.

Essentially what I want is a sheet users will use that will have 2 buttons, Emails received and Emails completed. Everytime they receive an email they click a button and a value in a cell on a differant sheet increases by one.

I have been able to to do this using simple Macros : -
Sub Up_One()
     Range("Data!C5").Value = Range("Data!C5").Value + 1
End Sub
Sub Up_Two()
     Range("Data!D5").Value = Range("Data!D5").Value + 1
End Sub
But my issue is the data range, at present when clicked the value is written to cell C5 and D5. However, what I am after is a VLookup of sorts that will write to the cell based on the date in column B:B. In column B is a list of dates from today onwards, today being in B5, therefore when I press the button I want the data to be written to C5 and then tomorrow D5 etc.

So over time I would end up with a table like the following: -

Date Emails R Emails C
14/09 4 3
15/9 5 2

Thanks in advance!

Edit:
Title is best I could think of to describe problem!

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 can anyone help?

I'm looking to write a formula that sums the contents of cells based on the background colour of the cells. Is this possible?

Cheers for any help.

Insert a number of rows based on a value in a cell on active row

--------------------------------------------------------------------------------

Hey all. I am new to macros in excel. Hoping to find help. I have a
worksheet, 3 columns and lets say 1000 rows. Column A is a beginning
page number of a document, column B is its ending page number, and
column C is the number of pages calculated by subtracting Bx - Ay and
adding 1. Example:

...
1000 1001 1
1002 1005 4
1006 1007 2
1008 1008 1
...

I'd like to have a macro which would go line-by-line and insert an
appropriate number of rows in between so I can flesh-out the ranges of
values. Example result would be:

1000 1001 1

1002 1005 4

1006 1007 2

1008 1008 1

Any help would be greatly appreciated.

--
iRocco
------------------------------------------------------------------------
iRocco's Profile: http://www.excelforum.com/member.php...o&userid=26147
View this thread: http://www.excelforum.com/showthread...hreadid=394733

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


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