I am trying have the macro go from H7:H300 and if the value in the cell is "Grand" the I need it to border the top, right, and bottom. Not the left side and then keep going until all cells are checked.
View 1 Replies
I would like to accomplish 2 things in my Excel 2010 spreadsheet by click a cell which already has a number and formatting in it.
1. How do I change the color of the cell, the color of the number in it, and the border around it. (Make it look like I just pressed a button by clicking it.)
2. At the same time have the text in different cell and the number in the selected cell appear in another different cell. (Text in a different cell = A , and the number in the selected cell = 23, the value in the resulting cell be "A 23") Everything I would like to happen at the same time by clicking the selected cell. I would also like this to be done several times by clicking different cells and not changing the previously selected cells.
I have seen spreadsheet where the user has made the outline of the cell border non-visible. This seems like a fairly simple task, but I can't seem to find out how you format this type of border.
View 2 Replies View RelatedWhen I enter data (digits) into a cell, its left border
becomes thinner ! Where is the connection between format and content ?
I have a spreadsheet with DDE formulas in the cells. When the contents of any cell changes I want to DRAW A BORDER (NOT CHANGE THE BACKGROUND COLOR !!) AROUND THE CELL .. When another cell's value is updated I want to REMOVE the border which was around the"OLD" cell and now place a border around the most recently changed cell, etc.
Only the newest active cell should have a border around it !!!
How can I change its colour? Every time I try to it reverts to the automatic black. I do not want to change the automatic colour (not that I know how to) because black suits me for most of the cells; but there are some where I want a blue border.
This problem arose in conditional formatting but applies to ordinary formatting too. It occurs in 2007 and 2003.
I have an excel sheet that is pulling data from other sheet based on some cretaria...i want a macro that when ran on this excel fixes column width , makes cell border as "dotted" and outline the area with Thick Outline.
This is what a macro code should do ; Hope this makes sense...thanks.
1. Finds numeric values in rows falling in colum B,C,D,E , than border those cells as dotted (i.e. right click on cell - format column - Border - Style - 3rs on top left row).
2. After filling cell border style as mentioned above , macro should do a THICK BOX BORDER around a cells falling above dotted cells.. i.e. one big sqaure outlined contaiing dotted cells with numeric values.
3. Trick part , my sheet is divided into 2 parts , INTERNAL and EXTERNAL. So i have writen "INTERNAL" in A1 cell and EXTERNAL in 'A' row in last row after INTERNAL lines. So the THICK BOX BORDER as requested in pt 2 shud be separted ones for INTERNAL and sep from EXTERNAL.
I am trying to Formal my Cell Border automatically.
Lets say i have cells
A
B
B
A
A
A
These are single cells running downwards only. I want to format this 6 cells into 1 box using (outside borders) formating. I need to repeat this for every 6 cells into 1box...and continue on and on. Im doing this process manually...But i got like 100,000 cells...
Since I did not know how to do this I used the record macro cheating option.
Excel spit out this chunck of
I have a sheet which automatically adds entrys to the sheet depending on what team they are on, all i really want to do is colour and border to the last row only, i know this should be able to get done in conditional formationing some how but im unsure.
I have got it in some of the cells with text in, but the ones with numbers in remain the same, if i can remember i used con formating and done, if the cell value is greater than <> then my formating, but numbers remain the same.
Why is the VBA for putting a simple border around a cell so convoluted. For example cell A2:
Code:
Sub CellBorder()
Range("A2").Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
[Code] ........
So many lines to put a simple - single line border (square) around a cell.
Is there any way to change the border color of a cell by running a subroutine? Here is my example:
Col 1
Col 2
1
2
3
4
I have an existing table with data that is updated daily ... more rows are added. Currently, the cell border is black. I would like to run a macro such that the column header is shaded grey and the borders turn into a shade of gray. I am interested in the 35% grey.
I want to add borders to a range depending on the value in the "a" column. Normally I want a left border in cell "a", a right border in cell "h" and bottom border in cells "a" to "h". However if the value in cell "a" is the same as the cell below I do not want the bottom border.
Cell "a" contains the names of customers using our facilities each day. Some customers use several facilities on the same day. I want the borders to encompass all facilities used by each customer per day e.g. range "a1:h8" might need bottom borders on all rows, because they are single customers using only one facility that day but ranges "a9:h11" would require bottom borders only on row 11, because that customer is using three of our facilities that day.
Set Rng = ActiveSheet.Range("a:h").SpecialCells(xlCellTypeConstants)
change the Range to "a:h"
and:-
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)..........
I have a dynamic table (rows regularly added or deleted) that is generated automatically based on choices made by the user in another worksheet. I need a function that will examine all the cells in one column, and when it finds a difference between two cell entries, it will change the border between these two entries to dark (medium weight), and then extend this border across the table. The end result should be that wherever the cell contents differ within a column, the table is divided by a darker border across the entire row.
View 9 Replies View Relatedhow to filter rows of data, in ascending or descending order, whilst still retaining formatting (in my case cell borders) and conditional formatting?
At present, when I filter rows in ascending / descending order, the cell formatting & conditional formatting stays in it's original position, rather than moving with the cell. I can't find a way to resolve this.
I'm trying to double-click a cell to edit the contents, and I am off a few pixels, it treats the double-click as a double-click on the cell border, not the cell contents, and the focus jumps to another cell in the direction of the border instead of going into edit mode. The effect is as if I had pressed Ctrl and the arrow in the direction of the border I clicked on.
The only way I've found to disable this is to turn off the option "Enable fill handle and cell drag-and-drop", but unchecking that also disables actually useful things, like dragging the corner of a formula call to copy it down, etc.
Is there a good way to disable this cell jumping when I accidentally double-click a cell border?
As i navigate excel, I was wondering how to change the background or highlight (instead of bold border) the active cell?
View 3 Replies View RelatedI have unprotected cells in excel where I need to change the content (number) and cell color, but I don't want the cell borders to change. I'm a beginner with vba. I've tried protect sheet and workbook options, but looks like I need a vba code and don't know where to start.
Win 7
Office 2007
my destination cells will have formulas like:
='E:My WorkTestData[CCC.xlsx]Input'!$S$31
Everything works fine. But I don't want the destination cells to display the full formula, but the actual Value only
I thought of a way to build a script that first builds the formula to one cell then Copy Paste Special value of that Cell to my desired Cell.
But when the code runs The cursor moves back and forth all the time...
I need to create a list of results based on a system entered on by a user with data already there. The first number they enter is the UCN (a number) then either indicate if it is a rail road or not. The values for this is Y or N. I need to have it where if they put Y then a certain formula is calculated and if they Indicate N then I have another formula calculated the results are put into the APN column. I also need to have it where I don't get an error if I have either of the cells (UNC or Railroad) ...
I uploaded a sample file of what I have so far : Book2.xlsx
I am trying to color cells with a special value in another cell.
I would like to use the value in K column to sort the color of the rows. As you can see I have done this manually there, but I would like to have it automatic so no one will put wrong color.
I tried to do this in VBE and started to put K7:k37 as range. then I used if formula to find if the correct value is in the cell. But then I had know idea what i was doing so I did some search here but could not find anything that I could use.
I need a hint for one complex validation. In column B I am having emails and need to validate that after ” @ “ the extension is yahoo.com. If something different from that I need the cell highlighted.
View 4 Replies View RelatedI have written the code below. And I found two problems:
1.The code stopped to work when I change from Paste to PasteSpecial.
Sub Copyfriction()
Dim DestWB As Workbook: Set DestWB = ActiveWorkbook
Dim DestWS As Worksheet: Set DestWS = ActiveSheet
Dim DestCLL As Range: Set DestCLL = ActiveCell
Dim UserChoice
UserChoice = Application.GetOpenFilename(FileFilter:="Text Files (*.xls),*.xls")
[Code] ........
2.As you see the right cell on the the destination sheet need to be selected. So Im planing to run the macro when double clicking the cell in question. I have tried the next
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$A$6" Then
Copyfriction
Cancel = True
End If
End Sub
I have something I've been trying to figure out for quite some time. I have to add the word 'special' at the end of various cells that has text already in it. Is there a macro that does this? Everytime I create one, it wipes out the text and adds the word 'special' instead. I need it to add the word 'special' at the END of the text that's already in there.
View 9 Replies View RelatedThis formula I want to apply it in another workbook. It split in different columns the content of a cell.
The formula is below:
[Code] .....
In cell A2 I have the following data:
|516582-001-99|414816-001-99|414816-003-99|516582-001-99|
If I apply the formula above in cells B2 to E2 it returns a blank cells. But if I delete the first "|" sign in the left side manually the formula works perfectly by splitting the cell into columns from B2 to E2. The issue here is that I have more than 300,000 records. Just imagine the amount of time invested in just deleting the first "|" at the left side.
I need a variation of the formula above that in first place delete the first "|" at the left side and after that continue with the proper work of the formula.
I'm trying to add a textbox at the current position (selected cell) with a set size, fill color, and border color. I found this: http://msdn.microsoft.com/en-us/libr...8(VS.80).aspx:
View 2 Replies View Relatedi have a device that produce its data as following:
NTFLog_D2013-02-12_T104016.csv
I need to divide the single column to 7 column as separated by semicolon ";"
Need VBA macro that will copy & paste (Special > AS VALUES) from one of two (Data A & B) sheets based on the contents of a validation cell ($D$4) in a third (Report) sheet? The destination starting cell would be $F$11. ALSO - I'd like to have the Named Ranges "DataAExtract" & "DataBExtract" used in the code (for the COPY region) so I can see an example of how to reflect my actual named ranges in my working file.
The reason for doing this is that the "c.Characters...." lines in my conditional formatting macros (attached) are not working on cells containing formula output (in my working file the Report page is all populated by VLOOKUP results), but the macros run fine on hard-coded values. In my attached workbook, I'd like to have the "NEW" macro for the copy & paste step fire first in the sequence of macros running after the FORM button-click (control located in cell $D$5 of the Report sheet), whether that's by writing a new macro and calling mine before the new one ends, OR by consolidating all of my macros plus the new one into one smooth progression.
With this low-tech approach I can get updated VALUES into the report area once the user selects a data source and a customer on the report sheet. The COPY ranges in my working spreadsheet will update based on the selections made in the report page. I tried recording a macro and then modifying the recorded code to add the "If > Then" functionality I'm looking for, but I'm pretty green when it comes to VBA code and syntax.
I'm trying to find a macro that will copy data from the areas of B120:E179 and I120:K179 for example (linked to another worksheet within the workbook) and special paste (Values Only and skipping blank cells) it to the next available open cell up top where basic data entry will be taking place B10:E29 and I10:K29. I need it to only copy/paste the rows with data (skipping all cells/rows with no data) and once it is finished coping I will need it to place an "X" in column M next to the row that it copied data from. I would also need it to reference the data in each row from B to E and if there is an entry say on B14 to E14 that matches it but if I10 to E29 are blank then paste that information on row 14. If it does not match or if those columns are full then paste on next available line.
I hope I'm making sense here. This is for a vehicle tracking log between checkpoints. Each driver and info will be listed on each row. Columns B through E will contain information for each driver: name, badge, #passengers, and vehicle #. The log lists location, time, and destination for outgoing travelers in columns F to H. Incoming info is listed on Columns I to K............
The below code adds a line to the next blank row. I would like to add a border to this row that the values are going into. Columns "A:G"
Code:
Private Sub CMD_Add_Click()
Dim rNextCl As Range
Set rNextCl = Worksheets("Main").Cells(Rows.Count, 2).End(xlUp).Offset(2, 0)
Worksheets("Main").Activate
rNextCl.Select
Dim iRow As Long
[Code]....
