0

need some help here...

I´m trying to help my wife automate excel spreadsheet shes uses, and im not being able to solve this...

What i want to achieve:

When i click in a range of cells, i want that a specific number decrease in a cell and increase in other cell, at the same time.

In the example:

I insert number 22 in cell D4 (this define the maximum cells that will become selected)

When i click on a cell in that range off cells, this cell becomes green and the number in cell D4 decrease by one and in cell F4 the number increase by one, and so on for every cell in that range in a maximum off 22 clicks.

If i click the same cell again, the cell comes to normal (without the green overlay), and the number in cells D4 increase by one and in F4 decrease by one, do, do the inverse.

After 22 cells are selected and overlayed with green, if i continue to click nothing happen, for me to be able to make another cell green, i need to remove one of the previous selected cells.

i hope you guys understand...

I only have a code to change cell color by double click , and the spreasheet dont use any other code, only conditional formatting and formulas.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Select Case Target.Interior.ColorIndex
    Case xlNone, 4: Target.Interior.ColorIndex = 4

End Select
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = xlNone
End Sub

Please check the examples pictures:

enter image description here
enter image description here
enter image description here

7
  • 1
    What your try to achieve requires macro. Asking for entire macro is off-topic here, being too broad. You need to try writing it yourself and may ask specific question during that if necessary. Apr 16, 2019 at 20:01
  • Your question is not fully clear. What defines the range of cells in which you can click cells in order to toggle their color. You have only mentioned D4 and F4 as the count down and count up cells. Are you looking for a VBA solution? Apr 16, 2019 at 20:02
  • Yes, im looking for a VBA solution, i have now a code that i turn the cell to green on double click. but i cannot make code to make it increase or decrease and limit the amount off cells i can select and change color... sorry, maybe im just beeing to ambicious. im not really an expert, im new at this, and im trying to help my wife... thats why im asking for help. thanks guys
    – T1a6o
    Apr 16, 2019 at 20:10
  • Please edit your question to include the code and explain exactly what the problem is with it Apr 16, 2019 at 20:11
  • Thanks cybernetic.nomad, i actually only have the code to change the cell color by double click.. like i said above, maybe i´m beeing to ambicious on this, its out of my league...
    – T1a6o
    Apr 16, 2019 at 20:15

1 Answer 1

0

I would do this with a couple of simple formulas on those Quantity cells.

First, in Quantity to Insert have the following: = 21 - Quantity_Inserted.

Second, in Quantity Inserted have the following: =COUNTIFS(Range, "x")

This will count the number of x in your Range and display it in the Quantity Inserted field, while at the same time subtracting that same amount from Quantity to Insert field.

I don't have a lot of experience (really, none at all) with VBA, but since you already have code to change the cell colour on a double-click, it's probably not going to take too much work to have it insert an 'x' instead.

Alternatively, you could have a conditional formatting applied to Range to detect whenever an x has been placed and colour both the text and background yellow effectively hiding the fact there's an x that's being counted.

5
  • Thanks alot for your help, i will try the formulas as you suggested, but the problem i see, is that in her spreadsheet, this cells will have numbers, like the days of the week or of the month.. so i cannot put the X, but maybe i can use your aproach to try to solve my problem. i will make some tests. thanks again
    – T1a6o
    Apr 16, 2019 at 20:58
  • Oooh, that would have been useful information to include in the question! I'll have a think and update my answer if I can figure it out... Apr 16, 2019 at 21:12
  • You are right, i´m sorry, im new at this... im learning :) but your answer helped me, i can already do what i want, the way you said. I just need a similar formula to detect color in a cell and count it, instead of the X. and it will work like a charm. thanks again :)
    – T1a6o
    Apr 16, 2019 at 21:15
  • Detecting colour in a cell is trickier, and will probably require some VBA. Apr 16, 2019 at 21:49
  • Yes, probably... I have found a code to detect color and change the value of cell by one, now I just need to implement the two codes. I'm making tests, and later I share the code here if I make it work. Thanks again
    – T1a6o
    Apr 17, 2019 at 14:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .