Free Microsoft Excel 2013 Quick Reference

Changing cell properties

Hi,

can anyone please tell me how to change the properties of cells/columns in code?

For instance, I have columns labeled Monday through Sunday. I would like the column that represents the current weekday to be highlighted on that day. So the entire 'Monday' column would be highlited in a different cell colour on Mondays, etc...this 'shading' would help when entering data.

I have found the WEEKDAY function but cannot find an example of code to change the cell color property.

many thanks to anyone who can help.


Post your answer or comment

comments powered by Disqus
I have a code snippet that I am using to change the properties of a cell:

With Range("FirstSlaveCell")
.Interior.Pattern = xlGray50
.Locked = True
.FormulaHidden = False
.Validation.InCellDropdown = False
End With

I want to replace it with a function/sub that looks like the one below but I
am getting a Run Time Error 424 Object Required. What am I doing wrong?

DisableCell (Range("FirstSlaveCell"))

Sub DisableCell(rng As Range)
With rng
.Interior.Pattern = xlGray50
.Locked = True
.FormulaHidden = False
.Validation.InCellDropdown = False
End With
End Sub

I have a button of which when pressed the active cell's value changes and colour.


	VB:
	
 Macro_Sick() 
    ActiveCell.FormulaR1C1 = "S" 
    Selection.Interior.ColorIndex = 3 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What i need to know is if there is a way to change a selection of cells properties in the same way, i can make it so the selections background colour changes but not all the selections values change.

Is there a way to do this?

Hi

I'm using the following code to print a cost value into a sheet following some simple calculations.

Sheet10.Cells(TotalRow, "AO").Value = tfees

The code works just fine, but when the data is copied, it changes the properties of the target cell from 'accounting' to 'Currency'.

Is there a way to output the data without changing the cell properties???

Many thanks
Richard

Hi Everyone,

i'm new to this forum and i have a question for you reguarding excel VBA. I want to declare in a module a function or sub that will change the CELL properties where the function has been called from if another cell contains certain information...example:

A B C D
1 100 5.5 =ONOFFCell(A1)
2 0.5 2 =ONOFFCEll(A2)
3 .
4 .

the ONOFFCELL (sub or function) must DISABLE ITSELF (CELL C1) so that people cannot enter anything in C1 cell IF the cell A1 value let say is bigger than "0". Then on each line i will have to repeat the same function etc. etc.

How can i do that? Can someone help me ?

Thank you!

William!

Hello. I am using VBA to create a new worksheet when it is run. However, when I use VBA to insert values into a particular cell, it uses the "General" formatting for the cells. In an instance of inserting "060706" into the cell, it chops the opening 0 off, and I need this 0 later on in my code. I know that changing the cell properties to "Text" will eliminate this problem. How do I do that using VBA?

Here is what I have now:


	VB:
	
 ExcelObj = CreateObject("Excel.Application") 
ExcelObj.Visible = True 
ExcelObj.Workbooks.Add 
 
Range("M1").Value = EmpNum 
Range("M2").Value = IMSP 
Range("M3").Value = SID 
Range("M4").Value = BDay 
Range("M5").Value = SSNo 

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


Here is the OR-FUNCTION Formula which works but I need to change
formula to use cell property references instead.

If Worksheets("Project Info").Range("$J2").Value = 1 Then
Worksheets("Update Schedule").Range("$B2").Value =
"=OR((YEAR(B$1)-YEAR('Project
Info'!$A2))*12+((MONTH(B$1)-MONTH('Project Info'!$A2)))=0" _
& "YEAR(B$1)-YEAR('Project
Info'!$A2))*12+((MONTH(B$1)-MONTH('Project Info'!$A2)))=12)"

HERE'S THE CODE FOR CHANGING TO USE CELL PROPERTY

I count number of rows in worksheet to find out how may times I must
loop through the formulas below in section FORMULAS FOR UPDATE
SCHEDULE. This works o'kay.

'COUNTING NUMBER OF CLIENTS ON PROJECT INFO WORKSHEET
Worksheets("Project Info").Activate
Dim r As Integer
Dim row As Range

With ActiveSheet
r = Worksheets("Project Info").Range("S1").Value + 1
Set row = Range(Cells(2, 1), Cells(r, 13))
Range(Cells(2, 1), Cells(r, 13)).Select
Worksheets("Formulas").Range("A2").Value = Selection.Rows.Count
Range("A1").Select
End With

'FORMULAS FOR UPDATE SCHEDULE
Worksheets("Update Schedule").Activate

Dim a As Integer
Dim sr As Integer
Dim z As Integer

'start row variable
sr = Worksheets("Formulas").Range("B2").Value
'per year variable
a = sr
'loop variable
z = Worksheets("Formulas").Range("A2").Value + 1

'Formula
For numclient = 1 To z
If Worksheets("Project Info").Cells(a, 10) = 12 Then
Worksheets("Update Schedule").Cells(a, 2) = "TRUE"
ElseIf Worksheets("Project Info").Cells(a, 10) = 1 Then
Worksheets("Update Schedule").Cells(a, 2) =
"=OR((YEAR(Cells(1,2))-YEAR(Worksheets("Project Info").Cells(a,1))*12"
_
& "+((MONTH(Cells(1,2))-MONTH(Worksheets("Project
Info").Cells(a,1)))=0," _
& "YEAR(Cells(1,2))-YEAR(Worksheets("Project
Info").Cells(a,1))*12" _
& "+((Cells(1,2)-MONTH('Worksheets("Project
Info").Cells(a,1)))=12)"
End If
a = a + 1
Next numclient

My problem is with the ElseIf statement using the OR-Function. I
receive a Compile error: Expected: end of statement at this point
...-YEAR(Worksheets("Project Info").Cells(a,1))... The compiler stops
at "Project

Info". I'm having a mental block on how to fix this.

Is there anyone who can help me. Thanks. Kylie

--
kylie
------------------------------------------------------------------------
kylie's Profile: http://www.excelforum.com/member.php...o&userid=35565
View this thread: http://www.excelforum.com/showthread...hreadid=553255

Hello,

I like to change the colorIndex of a cell by a function is it possible ?
The code following doesn't works:

Function Fncolor(Value)
Worksheets("Sheet").Cells(RowNbr, ColumnNbr).Font.ColorIndex = 3
Fncouleur = "nnnnnn"
End Function

With a cell containing '=Fncolor(5)' to call the function

The goal is to avoid Format / condition limitation to 3 conditions....
Thanks for your Help

Gilles P(FR)

Hi
I've tried to change cell's WIDTH programmatically, but it
seems that this property is ReadOnly.
Is there any way that will allow me to change cell's WIDTH
programmatically, say from 100 pixels to 200 pixels?

TIA

Hello,

I like to change the colorIndex of a cell by a function is it possible ?
The code following doesn't works:

Function Fncolor(Value)
Worksheets("Sheet").Cells(RowNbr, ColumnNbr).Font.ColorIndex = 3
Fncouleur = "nnnnnn"
End Function

With a cell containing '=Fncolor(5)' to call the function

The goal is to avoid Format / condition limitation to 3 conditions....
Thanks for your Help

Gilles P(FR)

Here is the OR-FUNCTION Formula which works but I need to change formula to use cell property references instead.

If Worksheets("Project Info").Range("$J2").Value = 1 Then
Worksheets("Update Schedule").Range("$B2").Value = "=OR((YEAR(B$1)-YEAR('Project Info'!$A2))*12+((MONTH(B$1)-MONTH('Project Info'!$A2)))=0" _
& "YEAR(B$1)-YEAR('Project Info'!$A2))*12+((MONTH(B$1)-MONTH('Project Info'!$A2)))=12)"

HERE'S THE CODE FOR CHANGING TO USE CELL PROPERTY

I count number of rows in worksheet to find out how may times I must loop through the formulas below in section FORMULAS FOR UPDATE SCHEDULE. This works o'kay.

'COUNTING NUMBER OF CLIENTS ON PROJECT INFO WORKSHEET
Worksheets("Project Info").Activate
Dim r As Integer
Dim row As Range

With ActiveSheet
r = Worksheets("Project Info").Range("S1").Value + 1
Set row = Range(Cells(2, 1), Cells(r, 13))
Range(Cells(2, 1), Cells(r, 13)).Select
Worksheets("Formulas").Range("A2").Value = Selection.Rows.Count
Range("A1").Select
End With

'FORMULAS FOR UPDATE SCHEDULE
Worksheets("Update Schedule").Activate

Dim a As Integer
Dim sr As Integer
Dim z As Integer

'start row variable
sr = Worksheets("Formulas").Range("B2").Value
'per year variable
a = sr
'loop variable
z = Worksheets("Formulas").Range("A2").Value + 1

'Formula
For numclient = 1 To z
If Worksheets("Project Info").Cells(a, 10) = 12 Then
Worksheets("Update Schedule").Cells(a, 2) = "TRUE"
ElseIf Worksheets("Project Info").Cells(a, 10) = 1 Then
Worksheets("Update Schedule").Cells(a, 2) = "=OR((YEAR(Cells(1,2))-YEAR(Worksheets("Project Info").Cells(a,1))*12" _
& "+((MONTH(Cells(1,2))-MONTH(Worksheets("Project Info").Cells(a,1)))=0," _
& "YEAR(Cells(1,2))-YEAR(Worksheets("Project Info").Cells(a,1))*12" _
& "+((Cells(1,2)-MONTH('Worksheets("Project Info").Cells(a,1)))=12)"
End If
a = a + 1
Next numclient

My problem is with the ElseIf statement using the OR-Function. I receive a Compile error: Expected: end of statement at this point ...-YEAR(Worksheets("Project Info").Cells(a,1))... The compiler stops at "Project

Info". I'm having a mental block on how to fix this.

Is there anyone who can help me. Thanks. Kylie

Hi, new to this forum, and pretty much new to vba programming.

I'm using Excel 2007, and I have a particular problem re. referencing the "current cell" data

My formula requires that I make reference to the cell property row and column.

I can write my formula so that it works correctly, however I would like the formula to update if the spreadsheet changes.

The trouble is, I have references the cell propery using activecell.cell and activecell.column.

If I make the formula update then if my cursor isn't on the cell where the formula is, the activecell references are wrong, and the formula then displays the wrong result.

Is there any way that I can reference the cell properties, no matter where the cursor is, so that my formula will work if I make it updateable?

Many thanks in advance.

I often read and write chunks of Excel data by reading them into a
variant array, process the data, and then write the changes back. As
most know this is a very effecient method.

I would like to do the same think with a range of cell properties. For
example I would like to be able to individually check which cells on a
sheet contain a formula. I am currently reading the cells into a range
object and looping through each cell in the range. The ones with a
formula I am applying a special format.

Is it possible to read the .HasFormula property into a variant array
and loop through the array instead of through the range. I think it
might be faster but cannot get it to work.

Any help or direction is appreciated.
Thanks
Darryl Smith

I want to know how to copy the formula without changing cell references

hi.

i need to Change a Cell Value, Only If It Matches Another Cell in my worksheet.

1:

Imagine i have cell C5 with a number of 10 in. Cell F5 must match that value (using a formula)

2:

Imagine i have cell D5 with a number of 11 in. Cell G5 must match that value (using a formula)

i have a box which loads up at startup and a number must be neterd for a specific office section. lets say we enter 10. Cell C5 gets filled with the Number 10. you can change cell F5, but not any other cells.

what i need is some sort of VBA code that will detect the name in cell C5 and will compare it to the value entered in cell F5. cell F5 will only allow data from cell C5 to be entered into it. because the value wont match the value in available cell D5, cell G5 should reject the entry.

please remember that a box appears at the start for entry. This means that excel will require the cells to be sort of dynamic and change the requirements the other way around when number 11 logs in, so that 11 cant enter data in F5.

can anybody help? i really need some advice!!!

Hello, is there any way to make a macro which will change cell fill based on date. I have a sheet with 365/366 rows for evry day in year...I need a macro which will fill a cell where are some values with red colour...

I understand the specification of a Function procedure does not provide for it to change the contents of cells. However, I have the need/preference to be able to change cell data by using a custom function (instead of a Sub) because of its ease of use and execution of multiple what-if scenarios - displayed jointly.

Q. Is there a way around this specification/limitation - any way? Are there different techniques (standard or non-standard) to achieve thisr goal?

Any advice appreciated?

Here is the scenario:
Column-A starts out empty.A user changes cell B5 and navigates to another cell (whether by clicking, tabbing, or enter-key, it doesn't matter).An "X" is placed in Column-A for that row.Preferably the user would then be taken to the cell they were navigating to (whether by mouse-click, tabbing, or the enter-key).This happens every time a change is made to a cell.I have no idea how to code for this.

I'm not sure what constraints I might have for exceptions. For instance, can it handle multiple row being pasted into or cleared at once, etc. (Deleting columns will not be allowed in this instance.)

Any advise or direction would be appreciated.

Thanks,
Drew

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

ADDENDUM:
I continued my search and found the instructions at http://www.ozgrid.com/VBA/track-changes.htm

My ultimate goal in putting an "X" in Column-A is to identify rows with changes, so they can then be copied into an "archive" worksheet. I'm thinking this VBA version of track changes would be much better than what I was earlier thinking.

I have a workbook with two worksheets, Data and Archives. I want to let a user make changes to data in the "Data" worksheet. Then when they save or press a button all rows with edits will be copied into the "Archives" worksheet.

Appending date/time and username stamps in the trailing columns would be very handy too.

Ideas?

Thanks in advance,
Andrew

Hi,

Does anyone know where I can find a comprehensive list of '.Cells()' properties that I can Test for/Apply to Excel Cells?

i.e.
Cells(x,y).NumberFormat

I want to set Conditional Formatting using VBA,
to test for:

Data Type (Character, Integer, Date, Decimal, Logical)
Field Length (x(50), 999, 99/99/9999, 999.99, Yes/No)

from an imported file.

Thanks in advance,

Neal

Hi,

whats the code to change cell formulas, so far i have this as a cell formula:


	VB:
	

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
i want my macro to change the cell formula to just =(COUNTIF('2005'!D5:D188,"IN"))

I'm sure i know how to do it but i just can't remember! Can someone give me a hint?

Hi there, I'm trying to automate a process whereby I can change cell colours according to their rankings. I know that Excel 2007+ has this capabilty in the Conditional Formatting but I'm stuck using 2003. I have tried using the RANK, MIN and MAX formulas in 2003 Conditional Formatting but in practice I'll be monitoring 10 shops and so cannot use this route either.

From the attached I have given examples of the desired outcomes where I would want to show the ranked variance and then also the ranked % variance - ideally with buttons so that I can toggle either blank / variance / % variance.

I will always have 10 shops to rank and there will be 10 difference products to analyse. Also note the the cost ranking is in the opposite order to the rest where the lower the variance the greener it is.
I assume that VBA is the way to go on this but I'm unsure on how to approach ranking each set of products individually and also to correctly lay out the colours.

Thanks in advance.

RankCellColour.xls

Can below piece of code be converted to use the "Cells" property:

	VB:
	
Range("B55:D55,I55").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have a following piece of code used to determine range for a chart:

	VB:
	
 myrange = Worksheets("Chart").Range(Cells(startx, column), Cells(weekx, column)) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but I would like to add a second range to it - like the cell "I55" in:

	VB:
	
Range("B55:D55,I55").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
As I'm using variables to determine the range I need to use the "Cells" property!
Is this possible!?

Hi all,

I am trying to understand what exactly the cells property does when used after a range object. For example if I use the following code:

Dim rng as Range
Set rng=Range("A2")
i=0
Do While i

It seems like this should work (change cells in C1:R100 to an annoying background color when edited), but it doesn't appear to do anything. Any ideas what is wrong?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Watch As Range
If Target.Cells.Count > 1 Then Exit Sub
Set Watch = Range("C1:R100")
If Not Intersect(Target, Watch) Is Nothing Then
Target.Interior.ColorIndex = 38
Target.Interior.Pattern = xlSolid
End If
End Sub

How can I change file properties:

-created 06/10/02 12.12.17
-modified 05/12/02 17.09.19
-accessed 06/12/02 19.12.22

of a lot of files *.xls and *.txt into my dir c:change?
All files should be:

-created 13/12/02 00.00.00
-modified 13/12/02 00.00.00
-accessed 13/12/02 00.00.00

Tia.

_________________
Maurizio

[ This Message was edited by: maurizio.rota on 2002-12-19 15:17 ]


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