Search : in
By :

Macro: Setting a cell value from another cell

Last answer on Sep 20, 2009 1:46:05 am BST Cashel, on Sep 19, 2009 1:36:03 pm BST 
 Report this message to moderators

Hello,
I am creating a spreadsheet for recording annual leave for a department.
I have set up a macro so that when a button is clicked, all the currently selected cells will change colour to denote annual leave.
However, I would like to set the value of each of the selected cells with the value in the cell at the start of the row (and index cell, storing a code)

The macro I have written used is:

Selection.Font.ColorIndex = 41
With Selection.Interior
.ColorIndex = 41
.Pattern = xlSolid
End With
Selection.FormulaR1C1 = "=RC2"

which works well enough, and sets all the selected cells background and foreground colour to blue and the value in the cell to (for example) =$B7 (if the 7th row was selected.)

However, I would like it to set the actual value in B7 rather than just reference the cell.

Any suggestions?

Configuration: Windows XP Internet Explorer 8.0

Best answers for « Macro: Setting a cell value from another cell » in :
[VBA] Detecting changes in cell Show[VBA] Detecting changes in cell The Event Change feature of a sheet will detects the change in the active cell but it gives no information about the content. The example given below will help you to find out if the cell was changed,...
Basic Excel Formulas ShowBasic Excel Formulas Below are some basic formulas for Microsoft excel: Basic formula : ADDITION cell A1 to A10 = sum (A1: A10) AVERAGE cell A1 to A10 = average (A1: A10) MAXIMUM cell A1 to A10 = max (A1: A10) MINIMUM...
EXCEL: The INTERSECTION function ShowEXCEL: The INTERSECTION function You have a table consisting of rows and columns. Want to know the value of the cell at the intersection of a particular row / column in your table Example: Your table: - starts C3 to F3 ... for...
Download USA Unlisted Cell Phone Numbers ShowHas you friend flown for America without telling you where he/she would settle? Don’t panic! USA Unlisted Cell Phone Numbers is a program which can help you. By using this program, you simply need to know his/her phone number in order to find...
Worksheet - Cells ShowThe Concept of a Cell A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also...
Spreadsheets - Cell Selection ShowCell Selection Spreadsheets are powerful tools for working with data. However, to work with data, it is necessary to have tools to rapidly choose the required cells. Line Selection An entire line can be chosen by clicking directly on the line...
Ask your question Reply

1

 venkat1926, on Sep 20, 2009 1:46:05 am BST

You want to color if any cell value is 87

use find function either as a formulla in speradsheet or in macro

example code statements 

dim cfind as range
set cfind=activesheet.usedrange.cells.find(what=87,lookat:=xlwho­le)
cfind.interior.colorindex =41

   
Reply to venkat1926
Ask your question Reply
They need your help