PDA

View Full Version : Putting values on cell based on cell colors



jameshbabu
01-13-2012, 10:50 PM
Dear all,

I would like to know is it possible to put values on each cell of a sheet or in selected cells based on the fill color of them.

also shades of a base color should be included, just like if i want to put a vaule "1" on green colored cells, it should be applied to all cells having light green or dark green.

what code should i enter in VB?

thanks in advance,,

mikerickson
01-13-2012, 11:39 PM
Formulas are the best way to distribute values from and to cells.
Conditional Formatting will give those cells the appropriate colors.

One problem with using color as input from the user is that changing a cell's color doesn't trigger any Excel events.
Down stream calculations and adjustments to a color change don't have an automatic trigger.

Excel is built to distribute values in cells via formulas.

jameshbabu
01-14-2012, 11:19 PM
i agree,

but now we are in a situation that all the user inputs are in colors. so i wrote a code like this


Sub numberofactivitiesplanned()
'
' Macro3 Macro
'

'

Dim row As Integer
Dim cOlumn As Integer
Dim totalrOw As Integer


totalrOw = Application.InputBox _
(Prompt:="Please enter total number of rows to scan", _
Title:="total number of rows", Type:=1)

Do Until row = totalrOw

Do Until cOlumn = 7

ActiveCell.Select
Selection.NumberFormat = "0"
If Selection.Interior.Color <> 8421504 Then
Selection.Value = "0"
ActiveCell.Offset(0, 1).Select
cOlumn = cOlumn + 1

Else

If Selection.Interior.Color = Selection.Offset(0, 1).Interior.Color Then
Selection.Value = "0"
ActiveCell.Offset(0, 1).Select
cOlumn = cOlumn + 1

Else
Selection.Value = "1"
ActiveCell.Offset(0, 1).Select
cOlumn = cOlumn + 1
End If
End If

Loop

ActiveCell.Offset(0, -7).Select
Selection.NumberFormat = "0"
cOlumn = 0

If Selection.Interior.Color <> 8421504 Then
Selection.Value = "0"
ActiveCell.Offset(1, 0).Select
row = row + 1
Else
If Selection.Interior.Color = Selection.Offset(0, 1).Interior.Color Then
Selection.Value = "0"
ActiveCell.Offset(1, 0).Select
row = row + 1

Else
Selection.Value = "1"
ActiveCell.Offset(1, 0).Select
row = row + 1
End If
End If
Loop


End Sub




it puts me a value "1" in the desired cell and it works fine.

now the problem i face is, i have to give color number each time when the shade changes, ie from blue to dark blue. can we do any thing to select a range of colors(like select all from blue to dark blue)?

jameshbabu
01-14-2012, 11:28 PM
thanks