PDA

View Full Version : Solved: Text boxes



Anomandaris
05-18-2009, 08:29 AM
Hi guys,

Is there any way by which I can view the value of a cell behind a text box?
Say I type in a bunch of numbers from A1:A4, then i insert a text box exactly over it, then get a macro to color the text box conditionally based on say value in A2.........end result im trying to get is a coloured text box with the numbers that were present before

any thoughts?

thanks

Bob Phillips
05-18-2009, 08:53 AM
Why not ditch the textbox and conditionally format the cell?

Anomandaris
05-18-2009, 09:44 AM
thanks, thats what i was working on, this guy at work used text boxes to make this heatmap grid, thats what threw me off, not sure why he did that and he's gone now and its password protected so I cant even see that code.

But i'll try without the darn boxes......

Say For a Range (A5:H5), If any cell value is 4, then cell.interior.colorindex = 5, (how do i do that for one cell above it, and one cell below it as well) e.g if A5 value = 4, then color the interior of A4:A6.

then I need a similar one for If A5 value = 4 then color cells A5:A7 (the 2 lower cells)


thanks

Bob Phillips
05-18-2009, 11:19 AM
Your cell references seem a bit mixed up to me, you have two seye which both contain A5:A6.

Anomandaris
05-18-2009, 12:50 PM
Oh that was just an example, I just need a code that would color 3 cells.

1. If cell value = 4, then color that cell and the two cells below it
2. If cell value = 5, then color that cell, the one above, and the one below
3. If cell value = 6 , color that cell, and the two cells above it

thanks

Bob Phillips
05-18-2009, 02:08 PM
But what if row 4 has value 4, row 5 has 5 and row 6 has 6, which takes precedence?

Anomandaris
05-19-2009, 01:42 AM
oh sorry i wasnt clear, these are separate scenarios. There can only be one at a time.
The 3 options ive shown above will never happen together, there will be option buttons on the page to select one of the scenarios.
So, none take precedence, I will not put all 3 in one macro




(This code is for an 8 by 8 grid, with each grid box having 3 vertical cells with different statistical data. I posted a workbook in another post called Color grid recently.
For the moment I'm just trying to figure out how to color a grid box, conditional upon one of the 3 cells in the grid box.).....after I work on it a bit more I will post my progress, then you'll see what I'm trying to do, its still at an early stage

Anomandaris
05-19-2009, 08:36 AM
I'm guessing I complicated things,

ok how about this, just this one scenario

If cell value > 0, then color that cell, the one above, and the one below



thanks

Anomandaris
05-19-2009, 10:28 AM
Its ok I got it, here's the way it worked

For Each cell In Rcurr
If IsNumeric(cell) Then
Select Case cell.Value
Case Is < 0: cell.Resize(3).Interior.ColorIndex = 9
Case Is = 0: cell.Offset(-1).Resize(3).Interior.ColorIndex = 7
Case Is > 0: cell.Offset(-2).Resize(3).Interior.ColorIndex = 5
End Select
End If
Next cell




thanks